` specifies the location to find the data. It can be
a path to a file or directory of partitioned files locally or on an
object store.
### Example: Parquet
Parquet data sources can be registered by executing a `CREATE EXTERNAL TABLE` SQL statement such as the following. It is not necessary to
provide schema information for Parquet files.
```sql
CREATE EXTERNAL TABLE taxi
STORED AS PARQUET
LOCATION '/mnt/nyctaxi/tripdata.parquet';
```
:::{note}
Statistics
: By default, when a table is created, DataFusion will read the files
to gather statistics, which can be expensive but can accelerate subsequent
queries substantially. If you don't want to gather statistics
when creating a table, set the `datafusion.execution.collect_statistics`
configuration option to `false` before creating the table. For example:
```sql
SET datafusion.execution.collect_statistics = false;
```
See the [config settings docs](../configs.md) for more details.
:::
### Example: Comma Separated Value (CSV)
CSV data sources can also be registered by executing a `CREATE EXTERNAL TABLE` SQL statement. The schema will be inferred based on
scanning a subset of the file.
```sql
CREATE EXTERNAL TABLE test
STORED AS CSV
LOCATION '/path/to/aggregate_simple.csv'
OPTIONS ('has_header' 'true');
```
### Example: Compression
It is also possible to use compressed files, such as `.csv.gz`:
```sql
CREATE EXTERNAL TABLE test
STORED AS CSV
COMPRESSION TYPE GZIP
LOCATION '/path/to/aggregate_simple.csv.gz'
OPTIONS ('has_header' 'true');
```
### Example: Specifying Schema
It is also possible to specify the schema manually.
```sql
CREATE EXTERNAL TABLE test (
c1 VARCHAR NOT NULL,
c2 INT NOT NULL,
c3 SMALLINT NOT NULL,
c4 SMALLINT NOT NULL,
c5 INT NOT NULL,
c6 BIGINT NOT NULL,
c7 SMALLINT NOT NULL,
c8 INT NOT NULL,
c9 BIGINT NOT NULL,
c10 VARCHAR NOT NULL,
c11 FLOAT NOT NULL,
c12 DOUBLE NOT NULL,
c13 VARCHAR NOT NULL
)
STORED AS CSV
LOCATION '/path/to/aggregate_test_100.csv'
OPTIONS ('has_header' 'true');
```
### Example: Partitioned Tables
It is also possible to specify a directory that contains a partitioned
table (multiple files with the same schema)
```sql
CREATE EXTERNAL TABLE test
STORED AS CSV
LOCATION '/path/to/directory/of/files'
OPTIONS ('has_header' 'true');
```
Tables that are partitioned using a Hive compliant partitioning scheme will have their columns and values automatically
detected and incorporated into the table's schema and data. Given the following example directory structure:
```console
hive_partitioned/
├── a=1
│ └── b=200
│ └── file1.parquet
└── a=2
└── b=100
└── file2.parquet
```
Users can specify the top level `hive_partitioned` directory as an `EXTERNAL TABLE` and leverage the Hive partitions to query
and filter data.
```sql
CREATE EXTERNAL TABLE hive_partitioned
STORED AS PARQUET
LOCATION '/path/to/hive_partitioned/';
SELECT count(*) FROM hive_partitioned WHERE b=100;
+------------------+
| count(*) |
+------------------+
| 1 |
+------------------+
```
### Example: Unbounded Data Sources
We can create unbounded data sources using the `CREATE UNBOUNDED EXTERNAL TABLE` SQL statement.
```sql
CREATE UNBOUNDED EXTERNAL TABLE taxi
STORED AS PARQUET
LOCATION '/mnt/nyctaxi/tripdata.parquet';
```
Note that this statement actually reads data from a fixed-size file, so a better example would involve reading from a FIFO file. Nevertheless, once Datafusion sees the `UNBOUNDED` keyword in a data source, it tries to execute queries that refer to this unbounded source in streaming fashion. If this is not possible according to query specifications, plan generation fails stating it is not possible to execute given query in streaming fashion. Note that queries that can run with unbounded sources (i.e. in streaming mode) are a subset of those that can with bounded sources. A query that fails with unbounded source(s) may work with bounded source(s).
### Example: `WITH ORDER` Clause
When creating an output from a data source that is already ordered by
an expression, you can pre-specify the order of the data using the
`WITH ORDER` clause. This applies even if the expression used for
sorting is complex, allowing for greater flexibility.
Here's an example of how to use `WITH ORDER` clause.
```sql
CREATE EXTERNAL TABLE test (
c1 VARCHAR NOT NULL,
c2 INT NOT NULL,
c3 SMALLINT NOT NULL,
c4 SMALLINT NOT NULL,
c5 INT NOT NULL,
c6 BIGINT NOT NULL,
c7 SMALLINT NOT NULL,
c8 INT NOT NULL,
c9 BIGINT NOT NULL,
c10 VARCHAR NOT NULL,
c11 FLOAT NOT NULL,
c12 DOUBLE NOT NULL,
c13 VARCHAR NOT NULL
)
STORED AS CSV
WITH ORDER (c2 ASC, c5 + c8 DESC NULLS FIRST)
LOCATION '/path/to/aggregate_test_100.csv'
OPTIONS ('has_header' 'true');
```
Where `WITH ORDER` clause specifies the sort order:
```sql
WITH ORDER (sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
[, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...])
```
#### Cautions when using the WITH ORDER Clause
- It's important to understand that using the `WITH ORDER` clause in the `CREATE EXTERNAL TABLE` statement only specifies the order in which the data should be read from the external file. If the data in the file is not already sorted according to the specified order, then the results may not be correct.
- It's also important to note that the `WITH ORDER` clause does not affect the ordering of the data in the original external file.
If data sources are already partitioned in Hive style, `PARTITIONED BY` can be used for partition pruning.
```text
/mnt/nyctaxi/year=2022/month=01/tripdata.parquet
/mnt/nyctaxi/year=2021/month=12/tripdata.parquet
/mnt/nyctaxi/year=2021/month=11/tripdata.parquet
```
```sql
CREATE EXTERNAL TABLE taxi
STORED AS PARQUET
PARTITIONED BY (year, month)
LOCATION '/mnt/nyctaxi';
```
## CREATE TABLE
An in-memory table can be created with a query or values list.
CREATE [OR REPLACE] TABLE [IF NOT EXISTS] table_name AS [SELECT | VALUES LIST];
```sql
CREATE TABLE IF NOT EXISTS valuetable AS VALUES(1,'HELLO'),(12,'DATAFUSION');
CREATE TABLE IF NOT EXISTS valuetable(c1 INT, c2 VARCHAR) AS VALUES(1,'HELLO'),(12,'DATAFUSION');
CREATE TABLE memtable as select * from valuetable;
```
## DROP TABLE
Removes the table from DataFusion's catalog.
DROP TABLE [ IF EXISTS ] table_name;
```sql
CREATE TABLE users AS VALUES(1,2),(2,3);
DROP TABLE users;
-- or use 'if exists' to silently ignore if the table doesn't exist
DROP TABLE IF EXISTS nonexistent_table;
```
## CREATE VIEW
View is a virtual table based on the result of a SQL query. It can be created from an existing table or values list.
CREATE [ OR REPLACE ] VIEW view_name AS statement;
```sql
CREATE TABLE users AS VALUES(1,2),(2,3),(3,4),(4,5);
CREATE VIEW test AS SELECT column1 FROM users;
SELECT * FROM test;
+---------+
| column1 |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
+---------+
```
```sql
CREATE VIEW test AS VALUES(1,2),(5,6);
SELECT * FROM test;
+---------+---------+
| column1 | column2 |
+---------+---------+
| 1 | 2 |
| 5 | 6 |
+---------+---------+
```
## DROP VIEW
Removes the view from DataFusion's catalog.
DROP VIEW [ IF EXISTS ] view_name;
```sql
-- drop users_v view from the customer_a schema
DROP VIEW IF EXISTS customer_a.users_v;
```
## DESCRIBE
Displays the schema of a table, showing column names, data types, and nullable status. Both `DESCRIBE` and `DESC` are supported as aliases.
{ DESCRIBE | DESC } table_name
The output contains three columns:
- `column_name`: The name of the column
- `data_type`: The data type of the column (e.g., Int32, Utf8, Boolean)
- `is_nullable`: Whether the column can contain null values (YES/NO)
### Example: Basic table description
```sql
-- Create a table
CREATE TABLE users AS VALUES (1, 'Alice', true), (2, 'Bob', false);
-- Describe the table structure
DESCRIBE users;
```
Output:
```sql
+--------------+-----------+-------------+
| column_name | data_type | is_nullable |
+--------------+-----------+-------------+
| column1 | Int64 | YES |
| column2 | Utf8 | YES |
| column3 | Boolean | YES |
+--------------+-----------+-------------+
```
### Example: Using DESC alias
```sql
-- DESC is an alias for DESCRIBE
DESC users;
```
### Example: Describing external tables
```sql
-- Create an external table
CREATE EXTERNAL TABLE taxi
STORED AS PARQUET
LOCATION '/mnt/nyctaxi/tripdata.parquet';
-- Describe its schema
DESCRIBE taxi;
```
Output might show:
```sql
+--------------------+-----------------------------+-------------+
| column_name | data_type | is_nullable |
+--------------------+-----------------------------+-------------+
| vendor_id | Int32 | YES |
| pickup_datetime | Timestamp(Nanosecond, None) | NO |
| passenger_count | Int32 | YES |
| trip_distance | Float64 | YES |
+--------------------+-----------------------------+-------------+
```
The `DESCRIBE` command works with all table types in DataFusion, including:
- Regular tables created with `CREATE TABLE`
- External tables created with `CREATE EXTERNAL TABLE`
- Views created with `CREATE VIEW`
- Tables in different schemas using qualified names (e.g., `DESCRIBE schema_name.table_name`)