none
Getting error "Incorrect syntax near 'HADOOP'" while creating an external data source RRS feed

  • Question

  • Hi,

      I am getting "Incorrect syntax near 'HADOOP'" while trying to create an external data source for polybase data loads.

    Query:

    CREATE DATABASE SCOPED CREDENTIAL AzureStoragecred
    WITH
        IDENTITY = 'abcdblobstorage',
        SECRET = '******'
    ;

    CREATE EXTERNAL DATA SOURCE abcdblobstorageprimary
    WITH (
        TYPE = HADOOP,
        LOCATION = 'wasb://sdpdata@abcdblobstorage.blob.core.windows.net',
        CREDENTIAL = AzureStoragecred
    );

    Raj

    Wednesday, February 21, 2018 8:55 PM

All replies

  • Hey Raj,

    Are you sure that you are running this against the SQL DW?
    When I ran your script against a SQL DB or MASTER I got a repro. when I ran it against my data warehouse the objects were created successfully.


    Wednesday, February 21, 2018 11:30 PM
  • Hi Raj,

    You can check the correct syntax below based on your source and destination while creating external data source/table

    -- PolyBase only:  Hadoop cluster as data source   
    -- (on SQL Server 2016)  
    CREATE EXTERNAL DATA SOURCE data_source_name  
        WITH (   
            TYPE = HADOOP,
            LOCATION = 'hdfs://NameNode_URI[:port]'  
            [, RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]' ]  
            [, CREDENTIAL = credential_name ]
        )
    [;]  

    -- PolyBase only: Azure Storage Blob as data source   
    -- (on SQL Server 2016 and Azure SQL Data Warehouse)  
    CREATE EXTERNAL DATA SOURCE data_source_name  
        WITH (   
            TYPE = HADOOP,  
            LOCATION = 'wasb[s]://container@account_name.blob.core.windows.net'
            [, CREDENTIAL = credential_name ]
        )  
    [;]

    -- PolyBase only: Azure Data Lake Store
    -- (on Azure SQL Data Warehouse)
    CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
    WITH (
        TYPE = HADOOP,
        LOCATION = 'adl://<AzureDataLake account_name>.azuredatalake.net',
        CREDENTIAL = AzureStorageCredential
    );

    -- PolyBase only: Hadoop cluster as data source
    -- (on Parallel Data Warehouse)
    CREATE EXTERNAL DATA SOURCE data_source_name
        WITH ( 
            TYPE = HADOOP, 
            LOCATION = 'hdfs://NameNode_URI[:port]'
            [, JOB_TRACKER_LOCATION = 'JobTracker_URI[:port]' ]
        )
    [;]

    -- PolyBase only: Azure Storage Blob as data source 
    -- (on Parallel Data Warehouse)
    CREATE EXTERNAL DATA SOURCE data_source_name
        WITH ( 
            TYPE = HADOOP,
            LOCATION = 'wasb[s]://container@account_name.blob.core.windows.net'
        )
    [;]

    -- Elastic Database query only: a shard map manager as data source   
    -- (only on Azure SQL Database)  
    CREATE EXTERNAL DATA SOURCE data_source_name  
        WITH (   
            TYPE = SHARD_MAP_MANAGER,  
            LOCATION = '<server_name>.database.windows.net',  
            DATABASE_NAME = '\<ElasticDatabase_ShardMapManagerDb'>,  
            CREDENTIAL = <ElasticDBQueryCred>,  
            SHARD_MAP_NAME = '<ShardMapName>'  
        )  
    [;]  

    -- Elastic Database query only: a remote database on Azure SQL Database as data source   
    -- (only on Azure SQL Database)  
    CREATE EXTERNAL DATA SOURCE data_source_name  
        WITH (   
            TYPE = RDBMS,  
            LOCATION = '<server_name>.database.windows.net',  
            DATABASE_NAME = '<Remote_Database_Name>',  
            CREDENTIAL = <SQL_Credential>  
        )  
    [;]  

    -- Bulk operations only: Azure Storage Blob as data source   
    -- (on SQL Server 2017 or later, and Azure SQL Database).
    CREATE EXTERNAL DATA SOURCE data_source_name  
        WITH (   
            TYPE = BLOB_STORAGE,  
            LOCATION = 'https://storage_account_name.blob.core.windows.net/container_name'
            [, CREDENTIAL = credential_name ]
        )  


    Cheers,

    Amit Tomar

    ---------------------------------------------------

    Please mark this as answer if it solved your query

    Please vote this as helpful if it solved your query

    ---------------------------------------------------

    My Blog My Wiki Page

    Thursday, February 22, 2018 9:58 AM
  • Thanks Casey. Looks like i am running on DB instance.

    Thanks a lot

    Thursday, February 22, 2018 4:35 PM
  • As i understand from this problem statement.

    You were using Azure SQL DB and NOT Azure SQL Data Warehouse (DW) to set up external tables from ADLS or similar types of Data sources.

    Is there a way to set up external tables on Azure SQL Database from data that is hosted on Azure Data Lake Store?

    Thanks!

    Wednesday, September 12, 2018 2:09 PM
  • Hi Suvrat,

    Did you find the answer to your question about how to setup external tables on Azure SQL DB hosted on Azure Datalake store? 

    If Yes, can you point me to the reference?

    Thanks.

    Wednesday, April 24, 2019 9:07 PM