# Query External Data > Instructions on querying external data files in GreptimeDB, including creating external tables and running queries on various file formats like CSV and Parquet. # Query External Data ## Query on a file Currently, we support queries on `Parquet`, `CSV`, `ORC`, and `NDJson` format file(s). We use the [Taxi Zone Lookup Table](https://d37ci6vzurychx.cloudfront.net/misc/taxi+_zone_lookup.csv) data as an example. ```bash mkdir -p greptimedb_data/copy curl "https://d37ci6vzurychx.cloudfront.net/misc/taxi+_zone_lookup.csv" -o greptimedb_data/copy/taxi+_zone_lookup.csv ``` Create an external table: ```sql CREATE EXTERNAL TABLE taxi_zone_lookup with (location='taxi+_zone_lookup.csv',format='csv'); ``` :::tip NOTE In standalone deployments, external table locations that reference local files are sandboxed to the `storage.copy_root` directory (default `/copy`), so this example downloads the file into `greptimedb_data/copy` and uses a location relative to that directory. In distributed deployments, local file locations are not supported. See [Migrate Local SQL File Access](/user-guide/deployments-administration/migrate-local-sql-file-access.md) for details. ::: You can check the schema of the external table like follows: ```sql DESC TABLE taxi_zone_lookup; ``` ```sql +--------------------+----------------------+------+------+--------------------------+---------------+ | Column | Type | Key | Null | Default | Semantic Type | +--------------------+----------------------+------+------+--------------------------+---------------+ | LocationID | Int64 | | YES | | FIELD | | Borough | String | | YES | | FIELD | | Zone | String | | YES | | FIELD | | service_zone | String | | YES | | FIELD | | greptime_timestamp | TimestampMillisecond | PRI | NO | 1970-01-01 00:00:00+0000 | TIMESTAMP | +--------------------+----------------------+------+------+--------------------------+---------------+ 4 rows in set (0.00 sec) ``` :::tip Note Here, you may notice there is a `greptime_timestamp` column, which doesn't exist in the file. This is because when creating an external table, if we didn't specify a `TIME INDEX` column, the `greptime_timestamp` column is automatically added as the `TIME INDEX` column with a default value of `1970-01-01 00:00:00+0000`. You can find more details in the [create](/reference/sql/create.md#create-external-table) document. ::: Now you can query on the external table: ```sql SELECT `Zone`, `Borough` FROM taxi_zone_lookup LIMIT 5; ``` ```sql +-------------------------+---------------+ | Zone | Borough | +-------------------------+---------------+ | Newark Airport | EWR | | Jamaica Bay | Queens | | Allerton/Pelham Gardens | Bronx | | Alphabet City | Manhattan | | Arden Heights | Staten Island | +-------------------------+---------------+ ``` ## Query on a directory Let's download some data: ```bash mkdir -p greptimedb_data/copy/external curl "https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2022-01.parquet" -o greptimedb_data/copy/external/yellow_tripdata_2022-01.parquet curl "https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2022-02.parquet" -o greptimedb_data/copy/external/yellow_tripdata_2022-02.parquet ``` Verify the download: ```bash ls -l greptimedb_data/copy/external total 165368 -rw-r--r-- 1 wenyxu wheel 38139949 Apr 28 14:35 yellow_tripdata_2022-01.parquet -rw-r--r-- 1 wenyxu wheel 45616512 Apr 28 14:36 yellow_tripdata_2022-02.parquet ``` Create the external table: ```sql CREATE EXTERNAL TABLE yellow_tripdata with(location='external/',format='parquet'); ``` Run queries: ```sql SELECT count(*) FROM yellow_tripdata; ``` ```sql +-----------------+ | COUNT(UInt8(1)) | +-----------------+ | 5443362 | +-----------------+ 1 row in set (0.48 sec) ``` ```sql SELECT * FROM yellow_tripdata LIMIT 5; ``` ```sql +----------+----------------------+-----------------------+-----------------+---------------+------------+--------------------+--------------+--------------+--------------+-------------+-------+---------+------------+--------------+-----------------------+--------------+----------------------+-------------+---------------------+ | VendorID | tpep_pickup_datetime | tpep_dropoff_datetime | passenger_count | trip_distance | RatecodeID | store_and_fwd_flag | PULocationID | DOLocationID | payment_type | fare_amount | extra | mta_tax | tip_amount | tolls_amount | improvement_surcharge | total_amount | congestion_surcharge | airport_fee | greptime_timestamp | +----------+----------------------+-----------------------+-----------------+---------------+------------+--------------------+--------------+--------------+--------------+-------------+-------+---------+------------+--------------+-----------------------+--------------+----------------------+-------------+---------------------+ | 1 | 2022-02-01 00:06:58 | 2022-02-01 00:19:24 | 1 | 5.4 | 1 | N | 138 | 252 | 1 | 17 | 1.75 | 0.5 | 3.9 | 0 | 0.3 | 23.45 | 0 | 1.25 | 1970-01-01 00:00:00 | | 1 | 2022-02-01 00:38:22 | 2022-02-01 00:55:55 | 1 | 6.4 | 1 | N | 138 | 41 | 2 | 21 | 1.75 | 0.5 | 0 | 6.55 | 0.3 | 30.1 | 0 | 1.25 | 1970-01-01 00:00:00 | | 1 | 2022-02-01 00:03:20 | 2022-02-01 00:26:59 | 1 | 12.5 | 1 | N | 138 | 200 | 2 | 35.5 | 1.75 | 0.5 | 0 | 6.55 | 0.3 | 44.6 | 0 | 1.25 | 1970-01-01 00:00:00 | | 2 | 2022-02-01 00:08:00 | 2022-02-01 00:28:05 | 1 | 9.88 | 1 | N | 239 | 200 | 2 | 28 | 0.5 | 0.5 | 0 | 3 | 0.3 | 34.8 | 2.5 | 0 | 1970-01-01 00:00:00 | | 2 | 2022-02-01 00:06:48 | 2022-02-01 00:33:07 | 1 | 12.16 | 1 | N | 138 | 125 | 1 | 35.5 | 0.5 | 0.5 | 8.11 | 0 | 0.3 | 48.66 | 2.5 | 1.25 | 1970-01-01 00:00:00 | +----------+----------------------+-----------------------+-----------------+---------------+------------+--------------------+--------------+--------------+--------------+-------------+-------+---------+------------+--------------+-----------------------+--------------+----------------------+-------------+---------------------+ 5 rows in set (0.11 sec) ``` :::tip Note The query result includes the value of the `greptime_timestamp` column, although it does not exist in the original file. All these column values are `1970-01-01 00:00:00+0000`, because when we create an external table, the `greptime_timestamp` column is automatically added with a default value of `1970-01-01 00:00:00+0000`. You can find more details in the [create](/reference/sql/create.md#create-external-table) document. ::: ## Query nested JSON fields Use `FORMAT = 'JSON'` for newline-delimited JSON (NDJSON), with one JSON object per line. In a standalone deployment using the default data home, create a sample file under `greptimedb_data/copy`: ```bash mkdir -p greptimedb_data/copy/logs cat > greptimedb_data/copy/logs/requests.json <<'EOF' {"host":"web-01","request":{"status":500,"path":"/api","client":{"ip":"10.0.0.1"}}} {"host":"web-02","request":{"status":200,"path":"/health","client":{"ip":"10.0.0.2"}}} EOF ``` Omit the column definitions to infer the schema from the files: ```sql CREATE EXTERNAL TABLE logs WITH (LOCATION = 'logs/', FORMAT = 'JSON'); DESC TABLE logs; ``` The inferred column types are: ```text host String request Struct<"client": Struct<"ip": String>, "path": String, "status": Int64> greptime_timestamp TimestampMillisecond ``` The nested `request` object is a `Struct` column. The automatically added `greptime_timestamp` column is the time index and has the default value `1970-01-01 00:00:00+0000`. Use brackets to select nested fields and filter by their values: ```sql SELECT host, request['status'] AS status, request['path'] AS path FROM logs WHERE request['status'] >= 500; ``` ```text +--------+--------+------+ | host | status | path | +--------+--------+------+ | web-01 | 500 | /api | +--------+--------+------+ ``` Chain brackets for deeper fields, or use the equivalent `get_field()` function: ```sql SELECT host, request['client']['ip'] AS client_ip FROM logs; SELECT host, get_field(request, 'status') AS status FROM logs; ``` `request['status']` accesses a field inside the `request` struct. In contrast, `"request.status"` refers to a top-level column whose name contains a dot. Schema inference preserves nested objects as structs; it does not flatten them into dotted column names or convert them into `JSON` or `JSON2` columns.