access_time 8 months ago languageEnglish
more_vert

Spark SQL - Construct Table using Literals

visibility 61 comment 0

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 8 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn.

Want to contribute on Kontext to help others?

Learn more

More from Kontext

visibility 313
thumb_up 0
access_time 7 months ago
visibility 486
thumb_up 0
access_time 7 months ago
visibility 203
thumb_up 0
access_time 7 months ago