docs/source/user-guide/sql/ddl.md

<!---
  Licensed to the Apache Software Foundation (ASF) under one
  or more contributor license agreements.  See the NOTICE file
  distributed with this work for additional information
  regarding copyright ownership.  The ASF licenses this file
  to you under the Apache License, Version 2.0 (the
  "License"); you may not use this file except in compliance
  with the License.  You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing,
  software distributed under the License is distributed on an
  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
  KIND, either express or implied.  See the License for the
  specific language governing permissions and limitations
  under the License.
-->

# DDL

## CREATE EXTERNAL TABLE

Parquet data sources can be registered by executing a `CREATE EXTERNAL TABLE` SQL statement. It is not necessary
to provide schema information for Parquet files.

```sql
CREATE EXTERNAL TABLE taxi
STORED AS PARQUET
LOCATION '/mnt/nyctaxi/tripdata.parquet';
```

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
WITH HEADER ROW
LOCATION '/path/to/aggregate_simple.csv';
```

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
WITH HEADER ROW
LOCATION '/path/to/aggregate_test_100.csv';
```

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` query

```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 HEADER ROW
WITH ORDER (c2 ASC, c5 + c8 DESC NULL FIRST)
LOCATION '/path/to/aggregate_test_100.csv';
```

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.

```
/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.

<pre>
CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <b><i>table_name</i></b> AS [SELECT | VALUES LIST];
</pre>

```sql
CREATE TABLE IF NOT EXISTS valuetable AS VALUES(1,'HELLO'),(12,'DATAFUSION');

CREATE TABLE memtable as select * from valuetable;
```

## DROP TABLE

Removes the table from DataFusion's catalog.

<pre>
DROP TABLE [ IF EXISTS ] <b><i>table_name</i></b>;
</pre>

```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.

<pre>
CREATE VIEW <i><b>view_name</b></i> AS statement;
</pre>

```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.

<pre>
DROP VIEW [ IF EXISTS ] <b><i>view_name</i></b>;
</pre>

```sql
-- drop users_v view from the customer_a schema
DROP VIEW IF EXISTS customer_a.users_v;
```