Spark SQL - Construct Virtual Table in Memory using Values
insights Stats
warning Please login first to view stats information.
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
info Last modified by Raymond 3 years ago
copyright
This page is subject to Site terms.
comment Comments
No comments yet.