{"id":3493,"date":"2022-04-25T07:19:32","date_gmt":"2022-04-25T15:19:32","guid":{"rendered":"https:\/\/www.gudusoft.com\/?p=3493"},"modified":"2022-05-19T01:40:00","modified_gmt":"2022-05-19T09:40:00","slug":"data-lineage-create-external-table","status":"publish","type":"post","link":"https:\/\/www.gudusoft.com\/de\/data-lineage-create-external-table\/","title":{"rendered":"Datenherkunft: Externe Tabelle erstellen | Gudu SQLFlow"},"content":{"rendered":"

Datenherkunft: Externe Tabelle erstellen | Gudu SQLFlow<\/strong><\/h2>\n

In the data warehouse, the original data is always from the mass storage such as Amazon S3, Google Cloud Storage, and Microsoft Azure, and those data will be loaded into the system such as BigQuery, Snowflake, Redshift, and Microsoft Azure. One of the methods used to load or use those data is using the create external table SQL statement. The data lineage from the external file to the external table can be captured easily by the Gudu SQLFlow<\/strong> by parsing the create external table SQL query. With this data on hand, Gudu SQLFlow<\/strong> greatly simplifies the ability to trace errors back to the root cause in a data analytics process.<\/p>\n

\"Datenherkunft:

Datenherkunft: Externe Tabelle erstellen<\/p><\/div>\n

In the article, we will introduce the create external table SQL statement used in BigQuery, Snowflake, Redshift, and Microsoft Azure, and see the data lineage generated by the Gudu SQLFlow<\/strong> after analyzing the SQL script.<\/p>\n

BigQuery create external table<\/h3>\n

External tables let BigQuery query data that is stored outside of BigQuery storage. For more information about external tables, see introduction to external data sources<\/a><\/strong>.<\/p>\n

Gudu SQLFlow<\/strong> can analyze BigQuery create external table statement and create the Datenherkunft<\/a><\/strong> after analyzing this SQL statement.<\/p>\n

Here is a BigQuery create external table SQL query:<\/p>\n

CREATE OR REPLACE EXTERNAL TABLE dataset.CsvTable\r\n(\r\n  x INT64,\r\n  y STRING\r\n)\r\nOPTIONS (\r\n  format = 'CSV',\r\n  uris = ['gs:\/\/bucket\/path1.csv'],\r\n  field_delimiter = '|',\r\n  max_bad_records = 5\r\n);<\/code><\/pre>\n
The data lineage generated for the above SQL here, as you can see the gs:\/\/bucket\/path1.csv file stored at Google Storage is treated as the source of the dataset.CsvTable external table.<\/p>\n
<\/figure>\n
\n
\"Datenherkunft:<\/a>
Datenherkunft: Externe Tabelle erstellen<\/figcaption><\/figure>\n<\/div>\n
Snowflake create external table<\/h3>\n
Snowflake creates an external table to reads data from a set of one or more files in a specified external stage and outputs the data in a single VARIANT column. Create external table statement<\/strong><\/a> creates a new external table in the current\/specified schema or replaces an existing external table.<\/p>\n
Before creating an external table, we need to create an external stage<\/strong><\/a> for the storage location where the data files are stored.<\/p>\n
create stage s1\r\n  url='s3:\/\/mybucket\/files\/logs\/'\r\n  ...\r\n  ;<\/code><\/pre>\n
Create the partitioned external table:<\/p>\n
create external table et1(\r\n date_part date as to_date(split_part(metadata$filename, '\/', 3)\r\n   || '\/' || split_part(metadata$filename, '\/', 4)\r\n   || '\/' || split_part(metadata$filename, '\/', 5), 'YYYY\/MM\/DD'),\r\n timestamp bigint as (value:timestamp::bigint),\r\n col2 varchar as (value:col2::varchar))\r\n partition by (date_part)\r\n location=@s1\/logs\/\r\n auto_refresh = true\r\n file_format = (type = parquet)\r\n aws_sns_topic = 'arn:aws:sns:us-west-2:001234567890:s3_mybucket';<\/code><\/pre>\n
Gudu SQLFlow can process Snowflake create external table query statement and build a data lineage between the files in the external stage and the external table like this:<\/p>\n
<\/figure>\n
\n
\"Datenherkunft:<\/a>
Datenherkunft: Externe Tabelle erstellen<\/figcaption><\/figure>\n<\/div>\n
In Snowflake, data from external files can also be moved to the table using copy into<\/a><\/strong> SQL statement and Gudu SQLFlow can also detect the data lineage in the copy into SQL query statement.<\/p>\n
SQL Server and Azure Synapse Analytics<\/h2>\n
Create external table command creates an external table for PolyBase to access data stored in a Hadoop cluster or Azure blob storage PolyBase external table that references data stored in a Hadoop cluster or Azure blob storage.<\/p>\n
In Azure Synapse Analytics, the created external table:<\/p>\n