How to Convert MySQL SELECT INTO OUTFILE and LOAD DATA INFILE to Databricks SQL?

[Akshay_Petkar]

Hi Community,

I have some existing MySQL code :

SELECT * FROM [table_name]
INTO OUTFILE 'file_path'
FIELDS TERMINATED BY '\t'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

LOAD DATA INFILE 'file_path' REPLACE INTO TABLE [database].[table_name]
FIELDS TERMINATED BY '\t'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

How do I convert this exactly into Databricks SQL? I just want the equivalent syntax in Databricks SQL
Thanks!

[krishnakhadka28]

Databricks SQL does not directly support MySQL’s SELECT INTO OUTFILE or LOAD DATA INFILE syntax. However, equivalent functionality can be achieved using Databricks features like saving to and reading from external locations like dbfs, s3 etc. I have used s3 when I wanted to load some master data in csv format from the s3 source.

CREATE OR REPLACE TEMP VIEW my_view AS SELECT * FROM my_table;

Example of SELECT INTO OUTFILE equivalent

COPY INTO 'dbfs:/mnt/output/my_table/' FROM my_view FILEFORMAT = CSV FORMAT_OPTIONS ('header' = 'true');

Example of LOAD DATA INFILE equivalent

COPY INTO my_table FROM 'dbfs:/mnt/input/my_table.csv' FILEFORMAT = CSV FORMAT_OPTIONS ('header' = 'true');