Skip to content

Instantly share code, notes, and snippets.

@casperlehmann

casperlehmann/Azure Synapse Analytics query data lake

Created June 11, 2020 07:51
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save casperlehmann/0cc1b2c1b7e56b6fa10ace784b95a313 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save casperlehmann/0cc1b2c1b7e56b6fa10ace784b95a313 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. casperlehmann created this gist Jun 11, 2020.
    42 changes: 42 additions & 0 deletions Azure Synapse Analytics query data lake
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,42 @@
    DROP EXTERNAL TABLE tests_external_table
    DROP EXTERNAL FILE FORMAT file_format_name
    DROP EXTERNAL DATA SOURCE extdatasource;
    DROP DATABASE SCOPED CREDENTIAL ADLSCredential;

    CREATE DATABASE SCOPED CREDENTIAL ADLSCredential
    WITH IDENTITY = 'User Identity',
    SECRET = '...............=='


    CREATE EXTERNAL DATA SOURCE extdatasource
    WITH
    ( LOCATION = 'abfss://data-lake-name@storageaccountname.dfs.core.windows.net/path/to/data/dir'
    , CREDENTIAL = ADLSCredential
    , TYPE = HADOOP
    );

    CREATE EXTERNAL FILE FORMAT file_format_name
    WITH (
    FORMAT_TYPE = DELIMITEDTEXT
    --[ , DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec' ]
    , FORMAT_OPTIONS (
    FIELD_TERMINATOR = '|'
    , STRING_DELIMITER = ''
    , First_Row = 1
    , USE_TYPE_DEFAULT = TRUE
    , Encoding = 'UTF8'
    )
    );

    CREATE EXTERNAL TABLE tests_external_table
    (
    Test varchar(1000)
    )
    WITH (
    LOCATION = '/path/in/subfolders/filename.csv',
    DATA_SOURCE = extdatasource,
    FILE_FORMAT = file_format_name
    )
    GO

    SELECT * FROM tests_external_table