Spark SQL - Construct Virtual Table in Memory using Values

In Spark SQL, virtual table can be constructed directly from constant values (literals) using SELECT statement.

Single column

spark-sql> SELECT StrColumn FROM VALUES ('abc'),('def'),('ghi') table1(StrColumn);
StrColumn
abc
def
ghi

Multiple columns

spark-sql> SELECT ID,StrColumn FROM VALUES (1,'abc'),(2,'def'),(3,'ghi') table1(ID,StrColumn);
2020-12-28 16:06:21,818 INFO codegen.CodeGenerator: Code generated in 307.0018 ms
ID      StrColumn
1       abc
2       def
3       ghi
Time taken: 2.726 seconds, Fetched 3 row(s)
2020-12-28 16:06:21,852 INFO thriftserver.SparkSQLCLIDriver: Time taken: 2.726 seconds, Fetched 3 row(s)

Construct table using function

The following code snippet constructs a table from splitting of literal string:

spark-sql> SELECT explode(split('a,b,c', ','));
col
a
b
c

Sample table with DATE literal columns

Sample 1:

spark-sql> SELECT * FROM VALUES
         > (101,10.01, DATE'2021-01-01'),
         > (101,102.01, DATE'2021-01-01'),
         > (102,93., DATE'2021-01-01'),
         > (103,913.1, DATE'2021-01-02'),
         > (101,900.56, DATE'2021-01-03')
         > AS TXN(ACCT,AMT, TXN_DT);
ACCT    AMT     TXN_DT
101     10.01   2021-01-01
101     102.01  2021-01-01
102     93.00   2021-01-01
103     913.10  2021-01-02
101     900.56  2021-01-03

Sample 2:

spark-sql> SELECT * FROM VALUES
         > (101,10.01, DATE'2021-01-01'),
         > (101,102.01, DATE'2021-01-01'),
         > (102,93., DATE'2021-01-01'),
         > (103,913.1, DATE'2021-01-02'),
         > (102,913.1, DATE'2021-01-02'),
         > (101,900.56, DATE'2021-01-03')
         > AS TXN(ACCT,AMT, TXN_DT);
ACCT    AMT     TXN_DT
101     10.01   2021-01-01
101     102.01  2021-01-01
102     93.00   2021-01-01
103     913.10  2021-01-02
102     913.10  2021-01-02
101     900.56  2021-01-03