2
0
Table Creation and Population: In this post we will discuss about the various ways to create a table in snowflake and populate these tables without using COPY command.
Though this post is very basic, but it would be good for the beginners or learners. They can implement these ways to create and populate the tables immediately. This would help them in testing perform in their local organization or free trial account.
Please note, Based on my understanding, I tried to include multiple ways for table creation but yes there can be other alternative approaches and suggestions are welcome as it would be learning curve for all as well.
Method1: TABLE(GENERATOR(ROWCOUNT) .
This way we are inserting 300M records in table and it took only 15 seconds on Large warehouse.
- Use TABLE(GENERATOR(ROWCOUNT) to generate the ROWS at run time.
- Random(),SEQ8() to generate the column values at Run time.
CREATE OR REPLACE TABLE INVOICE (
INVC_NO BIGINT,
INVC_CD VARCHAR,
AMT INTEGER
);
INSERT INTO INVOICE
SELECT
(SEQ8())::BIGINT AS INVC_NO
,randstr(2, random())::VARCHAR INVC_CD
,UNIFORM(1,999999,RANDOM(10003))::INTEGER AMT
FROM TABLE(GENERATOR(ROWCOUNT => 300000000));
Method2: TABLE(SPLIT_TO_TABLE)
- Splitting the values from SELECT clause at run time, This would be useful in particular scenario where you have data snippet from position based file or delimiter files. We can split the values at runtime based on the data.
create or replace table customers
as
select $1[0]::string customer_name, $1[1]::string fame_name, replace($1[2], ‘,’)::string address, $1[3]::string birth_place
from (
select split(x.value, ‘|’)
from table(split_to_table(‘Sachin Tendulkar|Master Blaster|104,302|Noida
Rohit Sharma|HITMAN|64,098|Mumbai
Saurav Ganguly|Dada|18,168|Kolkatta’, ‘\n’)) x
)
;
Method3: MULTIPLE INSERT
create or replace table Xerox_Cust(cust_id varchar2(10), category varchar2(30), Amount integer)
as select * from values
(‘100′,’Printer’,’3500′),
(‘100′,’Printer’,’300′),(‘100′,’Ink’,’300′),(‘100′,’Paper’,’300′)
Method4:CREATE TABLE FROM ANOTHER TABLE.
SET DATE_VARIABLE = ‘2020-12-01’;
SELECT $DATE_VARIABLE;
CREATE OR REPLACE TABLE DEMO_DB.PUBLIC.INVOICE_BKP AS
(SELECT * FROM DEMO_DB.PUBLIC.INVOICE WHERE DATE <= DATEADD(‘day’,-1,$DATE_VARIABLE));
Method5: CLONE the TABLE.
Method5: CLONE the TABLE.
CREATE TABLE INVOICE_CLONE CLONE INVOICE
Method6:EXTERNAL FUNCTION Call.
Method6:EXTERNAL FUNCTION Call.
create or replace table HTTP_DATA (
data varchar,
url varchar,
payload variant
);
insert into HTTP_DATA
select $1 data, $2 url, demo_db.public.EXT_UDF_fetchHTTPData( url ) payload
from
(values
(‘stations’, ‘https://gbfs.citibikenyc.com/gbfs/en/station_information.json’),
(‘regions’, ‘https://gbfs.citibikenyc.com/gbfs/en/system_regions.json’)
) ;
Method7:CROSS JOIN
Method7:CROSS JOIN
- Consider the smaller table and cross join itself to produce the ROWS.
- Consider the table has 100 rows in the table and by this formula we will be able to generate 30M rows in just 1 minute.100 x 100 x 100 x 30 = 30 Million rows
CREATE TABLE BIG_TABLE AS
SELECT
RANDOM() AS Customer_ID,
UUID_STRING() AS Customer_Name,
UUID_STRING() AS Customer_Email,
UUID_STRING() AS Customer_City,
UUID_STRING() AS Customer_State
FROM INVOICE1 A CROSS JOIN INVOICE1 B CROSS JOIN INVOICE1 C CROSS JOIN (SELECT TOP 30 * FROM INVOICE1) D;
Method8: Create the TABLE using LIKE.
Method8: Create the TABLE using LIKE.
- No data would be loaded. An empty table gets created.
CREATE TABLE NEW_TABLE LIKE EXISTING_TABLE.;
