I believe there's an SELECT INTO
-like syntax in Snowflake, but I am unable to find documentation or examples to use it.
CREATE TABLE raw_data (
Timestamp TIMESTAMP NOT NULL,
Date DATE NOT NULL,
UserID STRING,
Address STRING,
Phone STRING,
Value INTEGER
);
COPY INTO raw_data from 's3://my_bucket'
CREDENTIALS=(AWS_KEY_ID='XXXXX' AWS_SECRET_KEY='XXXX')
ON_ERROR=CONTINUE;
CREATE TABLE summary (
Date DATE NOT NULL,
UserID STRING,
Value INTEGER
);
INSERT INTO summary
SELECT Date, UserID, Value FROM raw_data
The above works, but rather than defining the table summary
I want to SELECT INTO
and have the SQL parser create the table for me.
Snowflake SQL: Query Syntax For robust relational database querying, Snowflake's Data Cloud platform has a Data Warehouse workload that supports the most prevalent standardized version of SQL (ANSI). It can also combine semi-structured data, such as JSON, with structured data stored in SQL.
Snowflake supports standard SQL, including a subset of ANSI SQL:1999 and the SQL:2003 analytic extensions. Snowflake also supports common variations for a number of commands where those variations do not conflict with each other.
You can use Create Table AS (CTAS) to accomplish this. In your case it would be:
CREATE TABLE SUMMARY AS
SELECT
Date
, UserID
, Value
FROM
raw_data;
Here is an example using no data that I have tested:
create table foo as select $1, $2
from
values ( 1, 'two' ), ( 3, 'four' ), ( 5, 'six' );
select * from foo;
Hope this helps!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With