Spark SQL - How can I create a temporary table with hardcoded values?

Question

I want to create a hardcoded table so that I can use it as a left join elsewhere.

Color
Red
Blue
Green
Yellow

I tried alot of combinations like

with TempTable as (
select * from ("red","blue","green","yellow") as color 
)

but this doesn't seem to work

docs.databricks.com/spark/latest/spark-sql/language-manual/… — Martin Smith
– Martin Smith, Commented Feb 12, 2021 at 22:23

ggordon · Accepted Answer · 2021-02-12 23:00:47Z

1

Temporary Approach

You may use inline tables

SELECT * from VALUES ("Red"),("Blue"), ("Green"), ("Yellow") as Colors (color)

Example As a Common Table Expressions (CTEs):

WITH colors as (
   SELECT * from VALUES ("Red"),("Blue"), ("Green"), ("Yellow") as Colors (color)
)
SELECT 
  tbl1.*,
  colors.color
FROM
  tbl1
LEFT JOIN
  colors ON tbl1.color = colors.color

Example as a subquery:

SELECT 
  tbl1.*,
  colors.color
FROM
  tbl1
LEFT JOIN
  (SELECT * from VALUES ("Red"),("Blue"), ("Green"), ("Yellow") as Colors (color)) colors
  ON tbl1.color = colors.color

Persisting this table

create table colors AS
SELECT * from VALUES ("Red"),("Blue"), ("Green"), ("Yellow") as Colors (color)

You could then query this as regular table

Reference

Inline Tables

edited Feb 12, 2021 at 23:00

answered Feb 12, 2021 at 22:54

ggordon

10.1k2 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mck · Accepted Answer · 2021-02-13 08:15:25Z

0

You can also create a temp view:

create or replace temp view TempTable as (
    select * from values ('Red'), ('Blue'), ('Green'), ('Yellow')
)

answered Feb 13, 2021 at 8:15

mck

42.7k13 gold badges44 silver badges62 bronze badges

Collectives™ on Stack Overflow

Spark SQL - How can I create a temporary table with hardcoded values?

2 Answers 2

Temporary Approach

Persisting this table

Reference

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Temporary Approach

Persisting this table

Reference

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related