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, json, and arrow.
COPY { table_name | query }
TO 'file_name'
[ STORED AS format ]
[ PARTITIONED BY column_name [, ...] ]
[ OPTIONS( option [, ... ] ) ]
STORED AS specifies the file format the COPY command will write. If this
clause is not specified, it will be inferred from the file extension if possible.
PARTITIONED BY specifies the columns to use for partitioning the output files into
separate hive-style directories. By default, columns used in PARTITIONED BY will be removed
from the output format. If you want to keep the columns, you should provide the option
execution.keep_partition_by_columns true. execution.keep_partition_by_columns flag can also
be enabled through ExecutionOptions within SessionConfig.
The output format is determined by the first match of the following rules:
Value of
STORED ASFilename extension (e.g.
foo.parquetimpliesPARQUETformat)
For a detailed list of valid OPTIONS, see Format Options.
Examples#
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' STORED AS PARQUET;
+-------+
| count |
+-------+
| 2 |
+-------+
Copy the contents of source_table to multiple directories
of hive-style partitioned parquet files:
> COPY source_table TO 'dir_name' STORED AS parquet, PARTITIONED BY (column1, column2);
+-------+
| count |
+-------+
| 2 |
+-------+
If the data contains values of x and y in column1 and only a in
column2, output files will appear in the following directory structure:
dir_name/
column1=x/
column2=a/
<file>.parquet
<file>.parquet
...
column1=y/
column2=a/
<file>.parquet
<file>.parquet
...
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' OPTIONS (MAX_ROW_GROUP_SIZE 10000000);
+-------+
| count |
+-------+
| 2 |
+-------+
INSERT#
Examples#
Insert values into a table.
INSERT INTO table_name { VALUES ( expression [, ...] ) [, ...] | query }
> INSERT INTO target_table VALUES (1, 'Foo'), (2, 'Bar');
+-------+
| count |
+-------+
| 2 |
+-------+
DELETE#
Removes rows from a table.
DELETE FROM table_name [ WHERE condition ]
DELETE returns the number of removed rows in a column named count.
If you omit the WHERE clause, DataFusion removes all rows.
DataFusion removes a row only if the condition is true for that row. SQL three-valued logic applies: if the condition evaluates to NULL, the row remains. For example, WHERE value > 15 keeps a row with a NULL value, because NULL > 15 is NULL.
Not all tables support DELETE. See Table support for DELETE and UPDATE.
Examples#
Remove the rows that match a condition:
> DELETE FROM target_table WHERE id > 1;
+-------+
| count |
+-------+
| 2 |
+-------+
Remove all rows:
> DELETE FROM target_table;
+-------+
| count |
+-------+
| 3 |
+-------+
UPDATE#
Changes the values of existing rows.
UPDATE table_name SET column = expression [, ...] [ WHERE condition ]
UPDATE returns the number of affected rows in a column named count.
If you omit the WHERE clause, DataFusion changes all rows. The three-valued logic of DELETE also applies here.
Each assignment expression reads the row values from before the statement. SET a = b, b = a therefore exchanges the two values.
Not all tables support UPDATE. See Table support for DELETE and UPDATE.
Examples#
Set one column in the rows that match a condition:
> UPDATE target_table SET name = 'Baz' WHERE id = 2;
+-------+
| count |
+-------+
| 1 |
+-------+
Set two columns, one from an expression:
> UPDATE target_table SET value = value * 2, name = 'Doubled' WHERE id < 3;
+-------+
| count |
+-------+
| 2 |
+-------+
Table support for DELETE and UPDATE#
Not all table providers support DELETE and UPDATE. In-memory tables created with CREATE TABLE support both statements. File-based tables created with CREATE EXTERNAL TABLE and views do not. Support for custom table providers depends on the provider.