\u30c7\u30fc\u30bf \u30a6\u30a7\u30a2\u30cf\u30a6\u30b9\u3067\u306f\u3001\u5143\u306e\u30c7\u30fc\u30bf\u306f\u5e38\u306b Amazon S3\u3001Google Cloud Storage\u3001Microsoft Azure \u306a\u3069\u306e\u5927\u5bb9\u91cf\u30b9\u30c8\u30ec\u30fc\u30b8\u304b\u3089\u53d6\u5f97\u3055\u308c\u3001\u305d\u308c\u3089\u306e\u30c7\u30fc\u30bf\u306f BigQuery\u3001Snowflake\u3001Redshift\u3001Microsoft Azure \u306a\u3069\u306e\u30b7\u30b9\u30c6\u30e0\u306b\u8aad\u307f\u8fbc\u307e\u308c\u307e\u3059\u3002\u3053\u308c\u3089\u306e\u30c7\u30fc\u30bf\u3092\u30ed\u30fc\u30c9\u307e\u305f\u306f\u4f7f\u7528\u3059\u308b\u305f\u3081\u306b\u4f7f\u7528\u3055\u308c\u308b\u65b9\u6cd5\u306e 1 \u3064\u306f\u3001create external table SQL \u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u3092\u4f7f\u7528\u3059\u308b\u3053\u3068\u3067\u3059\u3002\u5916\u90e8\u30d5\u30a1\u30a4\u30eb\u304b\u3089\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3078\u306e\u30c7\u30fc\u30bf\u7cfb\u7d71\u306f\u3001 Gudu SQLFlow<\/strong> create external table SQL \u30af\u30a8\u30ea\u3092\u89e3\u6790\u3057\u307e\u3059\u3002\u3053\u306e\u30c7\u30fc\u30bf\u3092\u624b\u5143\u306b\u7f6e\u3044\u3066\u3001 Gudu SQLFlow<\/strong> \u30c7\u30fc\u30bf\u5206\u6790\u30d7\u30ed\u30bb\u30b9\u3067\u30a8\u30e9\u30fc\u3092\u6839\u672c\u539f\u56e0\u307e\u3067\u8ffd\u8de1\u3059\u308b\u6a5f\u80fd\u304c\u5927\u5e45\u306b\u7c21\u7d20\u5316\u3055\u308c\u307e\u3059\u3002<\/p>\r\n \u3053\u306e\u8a18\u4e8b\u3067\u306f\u3001BigQuery\u3001Snowflake\u3001Redshift\u3001Microsoft Azure \u3067\u4f7f\u7528\u3055\u308c\u308b create external table SQL \u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u3092\u7d39\u4ecb\u3057\u3001 Gudu SQLFlow<\/strong> SQL\u30b9\u30af\u30ea\u30d7\u30c8\u3092\u5206\u6790\u3057\u305f\u5f8c\u3002<\/p>\r\n \u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3092\u4f7f\u7528\u3059\u308b\u3068\u3001BigQuery \u306f BigQuery \u30b9\u30c8\u30ec\u30fc\u30b8\u306e\u5916\u90e8\u306b\u4fdd\u5b58\u3055\u308c\u3066\u3044\u308b\u30c7\u30fc\u30bf\u3092\u30af\u30a8\u30ea\u3067\u304d\u307e\u3059\u3002\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u306e\u8a73\u7d30\u306b\u3064\u3044\u3066\u306f\u3001\u3092\u53c2\u7167\u3057\u3066\u304f\u3060\u3055\u3044\u3002 \u5916\u90e8\u30c7\u30fc\u30bf \u30bd\u30fc\u30b9\u306e\u7d39\u4ecb<\/a><\/strong>.<\/p>\r\n Gudu SQLFlow<\/strong> BigQuery create external table \u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u3092\u5206\u6790\u3057\u3001 \u30c7\u30fc\u30bf\u7cfb\u7d71<\/a><\/strong> \u3053\u306eSQL\u6587\u3092\u5206\u6790\u3057\u305f\u5f8c\u3002<\/p>\r\n BigQuery create external table SQL \u30af\u30a8\u30ea\u3092\u6b21\u306b\u793a\u3057\u307e\u3059\u3002<\/p>\r\n\r\n \u3053\u3053\u3067\u4e0a\u8a18\u306e SQL \u306b\u5bfe\u3057\u3066\u751f\u6210\u3055\u308c\u305f\u30c7\u30fc\u30bf\u7cfb\u7d71\u306f\u3001Google \u30b9\u30c8\u30ec\u30fc\u30b8\u306b\u4fdd\u5b58\u3055\u308c\u3066\u3044\u308b gs:\/\/bucket\/path1.csv \u30d5\u30a1\u30a4\u30eb\u304c dataset.CsvTable \u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u306e\u30bd\u30fc\u30b9\u3068\u3057\u3066\u6271\u308f\u308c\u308b\u3053\u3068\u304c\u308f\u304b\u308a\u307e\u3059\u3002<\/p>\r\n\r\n\r\n\r\n Snowflake \u306f\u3001\u6307\u5b9a\u3055\u308c\u305f\u5916\u90e8\u30b9\u30c6\u30fc\u30b8\u306e 1 \u3064\u4ee5\u4e0a\u306e\u30d5\u30a1\u30a4\u30eb\u306e\u30bb\u30c3\u30c8\u304b\u3089\u30c7\u30fc\u30bf\u3092\u8aad\u307f\u53d6\u308a\u3001\u5358\u4e00\u306e VARIANT \u5217\u306b\u30c7\u30fc\u30bf\u3092\u51fa\u529b\u3059\u308b\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002 \u5916\u90e8\u8868\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u306e\u4f5c\u6210<\/strong><\/a> \u73fe\u5728\u306e\/\u6307\u5b9a\u3055\u308c\u305f\u30b9\u30ad\u30fc\u30de\u306b\u65b0\u3057\u3044\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3059\u308b\u304b\u3001\u65e2\u5b58\u306e\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3092\u7f6e\u304d\u63db\u3048\u307e\u3059\u3002<\/p>\r\n\r\n\r\n\r\n \u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3059\u308b\u524d\u306b\u3001\u6b21\u306e\u3053\u3068\u3092\u884c\u3046\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059 \u5916\u90e8\u30b9\u30c6\u30fc\u30b8\u3092\u4f5c\u6210\u3059\u308b<\/strong><\/a> \u30c7\u30fc\u30bf\u30d5\u30a1\u30a4\u30eb\u304c\u4fdd\u5b58\u3055\u308c\u3066\u3044\u308b\u30b9\u30c8\u30ec\u30fc\u30b8\u306e\u5834\u6240\u3002<\/p>\r\n\r\n\r\n\r\n \u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u5316\u3055\u308c\u305f\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<\/p>\r\n\r\n\r\n\r\n Gudu SQLFlow \u306f Snowflake \u306e create external table \u30af\u30a8\u30ea \u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u3092\u51e6\u7406\u3057\u3001\u6b21\u306e\u3088\u3046\u306b\u5916\u90e8\u30b9\u30c6\u30fc\u30b8\u306e\u30d5\u30a1\u30a4\u30eb\u3068\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u306e\u9593\u306b\u30c7\u30fc\u30bf\u7cfb\u7d71\u3092\u69cb\u7bc9\u3067\u304d\u307e\u3059\u3002<\/p>\r\n\r\n\r\n\r\n Snowflake\u3067\u306f\u3001\u5916\u90e8\u30d5\u30a1\u30a4\u30eb\u304b\u3089\u306e\u30c7\u30fc\u30bf\u3082\u3001\u6b21\u3092\u4f7f\u7528\u3057\u3066\u30c6\u30fc\u30d6\u30eb\u306b\u79fb\u52d5\u3067\u304d\u307e\u3059\u3002 \u306b\u30b3\u30d4\u30fc<\/a><\/strong> SQL \u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u3068 Gudu SQLFlow \u306f\u3001SQL \u30af\u30a8\u30ea \u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u3078\u306e\u30b3\u30d4\u30fc\u3067\u30c7\u30fc\u30bf\u7cfb\u7d71\u3092\u691c\u51fa\u3059\u308b\u3053\u3068\u3082\u3067\u304d\u307e\u3059\u3002<\/p>\r\n\r\n\r\n\r\n Create external table \u30b3\u30de\u30f3\u30c9\u306f\u3001PolyBase \u304c Hadoop \u30af\u30e9\u30b9\u30bf\u30fc\u306b\u683c\u7d0d\u3055\u308c\u3066\u3044\u308b\u30c7\u30fc\u30bf\u306b\u30a2\u30af\u30bb\u30b9\u3059\u308b\u305f\u3081\u306e\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3001\u307e\u305f\u306f Hadoop \u30af\u30e9\u30b9\u30bf\u30fc\u307e\u305f\u306f Azure Blob Storage \u306b\u683c\u7d0d\u3055\u308c\u3066\u3044\u308b\u30c7\u30fc\u30bf\u3092\u53c2\u7167\u3059\u308b Azure Blob Storage PolyBase \u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<\/p>\r\n\r\n\r\n\r\n Azure Synapse Analytics \u3067\u306f\u3001\u4f5c\u6210\u3055\u308c\u305f\u5916\u90e8\u30c6\u30fc\u30d6\u30eb:<\/p>\r\n\r\n\r\n\r\n \u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3059\u308b\u306b\u306f\u3001\u6700\u521d\u306b\u5916\u90e8\u30c7\u30fc\u30bf \u30bd\u30fc\u30b9\u3092\u4f5c\u6210\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\r\n\r\n\r\n\r\n \u6b21\u306b\u3001\u6b21\u306e SQL \u30af\u30a8\u30ea\u3092\u4f7f\u7528\u3057\u3066\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<\/p>\r\n\r\n\r\n\r\n create external table \u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u3092\u89e3\u6790\u3057\u305f\u5f8c\u306b Gudu SQLFlow \u306b\u3088\u3063\u3066\u751f\u6210\u3055\u308c\u308b\u30c7\u30fc\u30bf\u7cfb\u7d71\u306f\u6b21\u306e\u3068\u304a\u308a\u3067\u3059\u3002<\/p>\r\n\r\n\r\n\r\n Amazon Redshift\u3001AWS Glue\u3001Amazon Athena\u3001\u307e\u305f\u306f Apache Hive \u30e1\u30bf\u30b9\u30c8\u30a2\u3067\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3067\u304d\u307e\u3059\u3002\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u304c AWS Glue\u3001Athena\u3001\u307e\u305f\u306f Hive \u30e1\u30bf\u30b9\u30c8\u30a2\u3067\u5b9a\u7fa9\u3055\u308c\u3066\u3044\u308b\u5834\u5408\u306f\u3001\u6700\u521d\u306b\u5916\u90e8\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3092\u53c2\u7167\u3059\u308b\u5916\u90e8\u30b9\u30ad\u30fc\u30de\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002\u305d\u306e\u5f8c\u3001Amazon Redshift \u3067\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u306a\u304f\u3066\u3082\u3001\u30c6\u30fc\u30d6\u30eb\u540d\u306b\u30b9\u30ad\u30fc\u30de\u540d\u306e\u30d7\u30ec\u30d5\u30a3\u30c3\u30af\u30b9\u3092\u4ed8\u3051\u308b\u3053\u3068\u3067\u3001SELECT \u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u3067\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3092\u53c2\u7167\u3067\u304d\u307e\u3059\u3002\u00a0<\/p>\r\n\r\n\r\n\r\n \u6b21\u306e\u4f8b\u3067\u306f\u3001\u6b21\u306e\u540d\u524d\u306e Amazon Redshift \u5916\u90e8\u30b9\u30ad\u30fc\u30de\u306b SALES \u3068\u3044\u3046\u540d\u524d\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002\u00a0 \u4e0a\u8a18\u306e SQL \u3092\u89e3\u6790\u3057\u305f\u5f8c\u3001Gudu SQLFlow \u306b\u3088\u3063\u3066\u751f\u6210\u3055\u308c\u305f\u30c7\u30fc\u30bf\u7cfb\u7d71\u306f\u6b21\u306e\u3068\u304a\u308a\u3067\u3059\u3002<\/p>\r\n\r\n\r\n\r\n \u79c1\u305f\u3061\u306e\u8a18\u4e8b\u3092\u304a\u8aad\u307f\u3044\u305f\u3060\u304d\u3042\u308a\u304c\u3068\u3046\u3054\u3056\u3044\u307e\u3059\u3002 \u30c7\u30fc\u30bf\u7cfb\u7d71\u306f\u3069\u306e\u3088\u3046\u306b\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u307e\u3059\u304b<\/strong>\u3001\u79c1\u305f\u3061\u306f\u3068\u3066\u3082\u5e78\u305b\u3067\u3059\u3002\u8a73\u3057\u304f\u77e5\u308a\u305f\u3044\u65b9\u306f \u30c7\u30fc\u30bf\u30ea\u30cd\u30fc\u30b8\u306f\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u307e\u3059<\/strong>\u3001\u79c1\u305f\u3061\u306f\u3042\u306a\u305f\u306b\u79c1\u305f\u3061\u306e\u30a6\u30a7\u30d6\u30b5\u30a4\u30c8\u3092\u8a2a\u554f\u3059\u308b\u3053\u3068\u3092\u304a\u52e7\u3081\u3057\u307e\u3059 Gudu SQLFlow<\/a><\/strong> \u8a73\u7d30\u306b\u3064\u3044\u3066\u306f\u3002 (2022 \u5e74 4 \u6708 25 \u65e5\u306b Ryan \u304c\u7de8\u96c6) <\/p>","protected":false},"excerpt":{"rendered":" Data Lineage Create External Table | Gudu SQLFlow 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 by parsing the create external table SQL query. With this data on hand, Gudu\u2026<\/p>","protected":false},"author":27,"featured_media":3544,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[31],"tags":[81,83,79,55,75,82,80],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"_links":{"self":[{"href":"https:\/\/www.gudusoft.com\/jp\/wp-json\/wp\/v2\/posts\/3493"}],"collection":[{"href":"https:\/\/www.gudusoft.com\/jp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gudusoft.com\/jp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gudusoft.com\/jp\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gudusoft.com\/jp\/wp-json\/wp\/v2\/comments?post=3493"}],"version-history":[{"count":50,"href":"https:\/\/www.gudusoft.com\/jp\/wp-json\/wp\/v2\/posts\/3493\/revisions"}],"predecessor-version":[{"id":6618,"href":"https:\/\/www.gudusoft.com\/jp\/wp-json\/wp\/v2\/posts\/3493\/revisions\/6618"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.gudusoft.com\/jp\/wp-json\/wp\/v2\/media\/3544"}],"wp:attachment":[{"href":"https:\/\/www.gudusoft.com\/jp\/wp-json\/wp\/v2\/media?parent=3493"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gudusoft.com\/jp\/wp-json\/wp\/v2\/categories?post=3493"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gudusoft.com\/jp\/wp-json\/wp\/v2\/tags?post=3493"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"\u30c7\u30fc\u30bf](\"https:\/\/www.gudusoft.com\/wp-content\/uploads\/2022\/04\/Data_Lineage_Create_External_Table.png\")
BigQuery \u304c\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210<\/h3>\r\n
CREATE OR REPLACE EXTERNAL TABLE dataset.CsvTable ( x INT64, y STRING ) OPTIONS ( format = 'CSV', uris = ['gs:\/\/bucket\/path1.csv'], field_delimiter = '|', max_bad_records = 5 );<\/code><\/pre>\r\n\r\n\r\n\r\n![\"\u30c7\u30fc\u30bf](\"https:\/\/www.gudusoft.com\/wp-content\/uploads\/2022\/04\/data-lineage-bigquery-create-external-table-1.png\")
<\/a>\r\nSnowflake\u306f\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u307e\u3059<\/h3>\r\n\r\n\r\n\r\n
\u30b9\u30c6\u30fc\u30b8 s1 \u306e\u4f5c\u6210 url='s3:\/\/mybucket\/files\/logs\/' ... ;<\/code><\/pre>\r\n\r\n\r\n\r\ncreate external table et1( date_part date as to_date(split_part(metadata$filename, '\/', 3) || '\/' || split_part(metadata$filename, '\/', 4) || '\/' || split_part(metadata$filename, '\/) ', 5), 'YYYY\/MM\/DD'), \u30bf\u30a4\u30e0\u30b9\u30bf\u30f3\u30d7 bigint as (value:timestamp::bigint), col2 varchar as (value:col2::varchar)) partition by (date_part) location=@s1\/logs\/ auto_refresh = true file_format = (type = parquet) aws_sns_topic = 'arn:aws:sns:us-west-2:001234567890:s3_mybucket';<\/code><\/pre>\r\n\r\n\r\n\r\n![\"\u30c7\u30fc\u30bf](\"https:\/\/www.gudusoft.com\/wp-content\/uploads\/2022\/04\/data-lineage-snowflake-create-external-table.png\")
<\/a>\r\nSQL Server \u3068 Azure Synapse Analytics<\/h2>\r\n\r\n\r\n\r\n
\r\n
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore WITH (TYPE = HADOOP, LOCATION = 'abfss:\/\/data@pbasetr.azuredatalakestore.net' )<\/code><\/pre>\r\n\r\n\r\n\r\nCREATE EXTERNAL TABLE [dbo].[DimProductexternal] ( [ProductKey] [int] NOT NULL, [ProductLabel] nvarchar NULL, [ProductName] nvarchar NULL ) WITH ( LOCATION='\/DimProduct\/' , DATA_SOURCE = AzureDataLakeStore , FILE_FORMAT = TextFileFormat , REJECT_TYPE = \u5024\u3001REJECT_VALUE = 0);<\/code><\/pre>\r\n\r\n\r\n\r\n![\"\"](\"https:\/\/www.gudusoft.com\/wp-content\/uploads\/2022\/05\/data-lineage-sqlserver-create-external-table-1-1024x119.png\")
<\/a>\r\nAWS Redshift \u304c\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210<\/h2>\r\n\r\n\r\n\r\n
\u30b9\u30da\u30af\u30c8\u30e9\u30e0<\/code>.\u30c7\u30fc\u30bf\u306f\u30bf\u30d6\u533a\u5207\u308a\u306e\u30c6\u30ad\u30b9\u30c8 \u30d5\u30a1\u30a4\u30eb\u3067\u3059\u3002<\/p>\r\n\r\n\r\n\r\n\u5916\u90e8\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002 \u300ct\u300d\u3067\u7d42\u4e86\u3059\u308b\u30d5\u30a3\u30fc\u30eb\u30c9\u306f\u3001\u30c6\u30ad\u30b9\u30c8\u30d5\u30a1\u30a4\u30eb\u306e\u5834\u6240\u300cs3:\/\/awssampledbuswest2\/tickit\/spectrum\/sales\/\u300d\u3068\u3057\u3066\u4fdd\u5b58\u3055\u308c\u307e\u3059 (\u300cnumRows\u300d=\u300c172000\u300d)\u3002<\/code><\/pre>\r\n\r\n\r\n\r\n![\"\"](\"https:\/\/www.gudusoft.com\/wp-content\/uploads\/2022\/05\/data-lineage-redshift-create-external-table.png\")
<\/a>\r\n\u7d50\u8ad6\u00a0<\/h2>\r\n\r\n\r\n\r\n
<\/p>\r\n\r\n