Spark SQL - Construct Table using Literals

access_time 19 days ago visibility15 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 9 days 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 or Twitter.

Want to publish your article on Kontext?

Learn more

More from Kontext

visibility 7
thumb_up 0
access_time 9 days ago

Spark LEAD function provides access to a row at a given offset that follows the current row in a window. This analytic function can be used in a SELECT statement to compare values in the current row with values in a following row. This function is like  Spark SQL - LAG Window Function .

visibility 10
thumb_up 0
access_time 5 days ago

Like other SQL engines, Spark also supports PIVOT clause. PIVOT is usually used to calculated aggregated values for each value in a column and the calculated values will be included as columns in the result set. PIVOT ( { aggregate_expression [ AS aggregate_expression_alias ] } [ , ... ] FOR ...

visibility 4
thumb_up 0
access_time 6 days ago

In article  Scala: Parse JSON String as Spark DataFrame , it shows how to convert JSON string to Spark DataFrame; this article show the other way around - convert complex columns to a JSON string using to_json function. Function ' to_json(expr[, options]) ' returns a JSON string with a ...