# EXTERNAL TABLE

Config path: `/<database>/<schema>/external_table/<name>.yaml`

Example:

```yaml
columns:
  dt:
    type: DATE
    expr: "to_date(split_part(metadata$filename, '/', 2))::date"
    comment: "Date of ingestion"

  id:
    type: NUMBER(38,0) NOT NULL
    expr: "$1:id::number(38,0)"

  name:
    type: VARCHAR(255) NOT NULL
    expr: "$1:name::varchar(255)"

location:
  stage: test_external_stage
  file_format: test_parquet_format

partition_by: [dt]

```

## Schema

* **columns** (dict)
  * *{key}* (ident) - column name
  * *{value}* (dict)
    * <mark style="background-color:red;">**type**</mark> (str) - full [data type](https://docs.snowddl.com/guides/data-types) with optional "NOT NULL" constraint (not enforced)
    * <mark style="background-color:red;">**expr**</mark> (str) - SQL expression describing column
    * **comment** (str)
* <mark style="background-color:red;">**location**</mark> (dict)
  * <mark style="background-color:red;">**stage**</mark> (ident) - stage name
  * **path** (str) - path prefix for files stage
  * **pattern** (str) - regular expression to filter files in stage
  * <mark style="background-color:red;">**file\_format**</mark> (ident) - [file format](https://docs.snowddl.com/basic/yaml-configs/file-format) for files in stage
* **partition\_by** (list)
  * *{items}* (ident) - column names for PARTITION BY
* **partition\_type** (str) - example: `USER_DEFINED`
* **auto\_refresh** (bool) - enable `AUTO_REFRESH` (default: `False`)
* **refresh\_on\_create** (bool) - refresh once immediately after creation (default: `False`)
* **aws\_sns\_topic** (str) - SNS topic for S3 bucket
* **table\_format** (str) - example: `DELTA`
* **integration** (ident) - notification [integration](https://docs.snowddl.com/guides/other-guides/integrations) name for Azure
* **comment** (str)
* **primary\_key** (list)
  * *{items}* (ident) - column names for PRIMARY KEY constraint
* **unique\_keys** (list)
  * *{items}* (list)
    * *{items}* (ident) - column names for UNIQUE KEY constraint
* **foreign\_keys** (list)
  * *{items} (dict)* - FOREIGN KEY definitions
    * **columns** (list)
      * *{items}* (ident) - column names from current table
    * **ref\_table** (ident) - reference table
    * **ref\_columns** (list)
      * *{items}* (ident) - column names from reference table

## Policy reference parameters

* **row\_access\_policy** (dict)
  * **policy\_name** (ident) - name of [ROW ACCESS POLICY](https://docs.snowddl.com/basic/yaml-configs/row-access-policy)
  * **columns** (list)
    * *{items}* (ident) - reference column names

## Usage notes

1. If automatic metadata refresh it not available for your cloud provider, it should be implemented separately. SnowDDL cannot refresh external table metadata for you.
2. **file\_format** can only be specified by name referencing to `FILE_FORMAT` object.

## Links

* [CREATE EXTERNAL TABLE](https://docs.snowflake.com/en/sql-reference/sql/create-external-table.html)
* [SHOW EXTERNAL TABLES](https://docs.snowflake.com/en/sql-reference/sql/show-external-tables.html)
* [DESC EXTERNAL TABLE](https://docs.snowflake.com/en/sql-reference/sql/desc-external-table.html)
* [Working with External Tables](https://docs.snowflake.com/en/user-guide/tables-external.html)
* [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/external_table.py)