DML

DML stands for “Data Manipulation Language” and relates to inserting and modifying data in tables.

COPY

Copies the contents of a table or query to file(s). Supported file formats are parquet, csv, and json and can be inferred based on filename if writing to a single file.

COPY { table_name | query } TO 'file_name' [ ( option [, ... ] ) ]

For a detailed list of valid OPTIONS, see Write Options.

Copy the contents of source_table to file_name.json in JSON format:

> COPY source_table TO 'file_name.json';
+-------+
| count |
+-------+
| 2     |
+-------+

Copy the contents of source_table to one or more Parquet formatted files in the dir_name directory:

> COPY source_table TO 'dir_name' (FORMAT parquet);
+-------+
| count |
+-------+
| 2     |
+-------+

Copy the contents of source_table to multiple directories of hive-style partitioned parquet files:

> COPY source_table TO 'dir_name' (FORMAT parquet, partition_by 'column1, column2');
+-------+
| count |
+-------+
| 2     |
+-------+

Run the query SELECT * from source ORDER BY time and write the results (maintaining the order) to a parquet file named output.parquet with a maximum parquet row group size of 10MB:

> COPY (SELECT * from source ORDER BY time) TO 'output.parquet' (ROW_GROUP_LIMIT_BYTES 10000000);
+-------+
| count |
+-------+
| 2     |
+-------+

INSERT

Insert values into a table.

INSERT INTO table_name { VALUES ( expression [, ...] ) [, ...] | query }
> INSERT INTO target_table VALUES (1, 'Foo'), (2, 'Bar');
+-------+
| count |
+-------+
| 2     |
+-------+