Querybook - SQL query to load data from a csv into database table

[Nish160]

Hi, I need to automate the process of data load into a new table in querybook using a sql query. I have tried to run following queries in querybook in order to load a data from csv into table:

CREATE TABLE project_arm.commissions_airlines_v3( Airline varchar(100), seat decimal, baggage decimal, start_date date, stop_date date ); BULK INSERT project_arm.commissions_airlines_v3 FROM '/Users/nis/Library/CloudStorage/OneDrive/nis/03-2023/AirAncillary_ CommissionsWithStartStopDate/commissions_airlines_V4.csv' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FORMAT='CSV');

------------OR--------- Select * into project_arm.commissions_airlines_v3 from OPENROWSET( BULK '/Users/nis/Library/CloudStorage/OneDrive/nis/03-2023/AirAncillary_ CommissionsWithStartStopDate/commissions_airlines_V4.csv', FORMATFILE = '/Users/nis/Library/CloudStorage/OneDrive/nis/03-2023/AirAncillary_ CommissionsWithStartStopDate/Test.fmt', FIRSTROW = 2)

---

But both the queries do not work in querybook. They give following error: line 1:1: mismatched input 'BULK'. Expecting: 'ALTER', 'ANALYZE', 'CALL', 'COMMENT', 'COMMIT', 'CREATE', 'DEALLOCATE', 'DELETE', 'DENY', 'DESC', 'DESCRIBE', 'DROP', 'EXECUTE', 'EXPLAIN', 'GRANT', 'INSERT', 'MERGE', 'PREPARE', 'REFRESH', 'RESET', 'REVOKE', 'ROLLBACK', 'SET', 'SHOW', 'START', 'TRUNCATE', 'UPDATE', 'USE',

Are 'BULK' as well as 'OPENROWSET' not supported by Querybook? If yes, then what is the alternative to do so?

[jczhong84]

It depends on what query engine you're using and whether it supports those.

[Nish160]

Yes, so I am using Querybook ..

and none of the above SQL queries are working.. I also tried running the following, but these also didn't work: LOAD DATA INFILE '/Users/nis/Library/CloudStorage/OneDrive/nis/03-2023/AirAncillary_ CommissionsWithStartStopDate/commissions_airlines_V4.csv' INTO TABLE project_arm.commissions_airlines_v3 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

COPY project_arm.commissions_airlines_v4 FROM '/Users/nis/Library/CloudStorage/OneDrive/nis/03-2023/AirAncillary_ CommissionsWithStartStopDate/commissions_airlines_V4.csv' DELIMITER ',';

So, I would like to understand if there's any other way than the UI upload in querybook to upload a csv file using SQL query? if yes, then please share the syntax for the same..

[jczhong84]

1. does any simple select query work on your Querybook?
2. what I meant by query engine is like, are you using mysql, presto, sparksql or any others? as different query engine has different syntax, you gotta check their official doc to see if they support bulk upload and what the syntax is.
3. Querybook also provides an experimental feature of Table Upload, you may ask your admin to set it up if you like to try.