Spark SQL - Construct Virtual Table in Memory using Values

event 2020-12-27 visibility 733 comment 0 insights
more_vert
insights Stats
Raymond Raymond Code Snippets & Tips

Code snippets and tips for various programming languages/frameworks. All code examples are under MIT or Apache 2.0 license unless specified otherwise. 

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
More from Kontext
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts