azure - 在 Azure SQL 数据仓库中创建外部表

Question

我想使用 Polybase 将 CSV 加载到 Azure SQL 数据仓库中。我创建了外部数据源和外部文件格式。由于位置错误，我无法创建外部表。我在下面附上脚本和屏幕截图。请让我知道此处可以应用哪些适当的代码。

 CREATE EXTERNAL TABLE [dbo].[exttest1]
( [description] [nvarchar](4000) NULL,
    [industry] [nvarchar](4000) NULL,
    [level] [nvarchar](4000) NULL,
    [size] [nvarchar](4000) NULL,
    [line_code] [nvarchar](4000) NULL,
    [value] [nvarchar](4000) NULL )
WITH
(
    LOCATION='store17/test2/SampleData.csv' , --my azure blob storage container
    DATA_SOURCE = ds1 ,
    FILE_FORMAT = FF1 ,
    REJECT_TYPE = VALUE ,
    REJECT_VALUE = 0
) ;

Answer 1

如果我们猜对了，您希望将 SampleData.csv 从博客存储加载到 Azure SQL 数据仓库。

当我们 create an external data source with CREDENTIAL option来自 Blob 存储:

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] TYPE = HADOOP ]
[ ; ]

位置值格式应为:

LOCATION = 'wasb[s]://<container>@<storage_account>.blob.core.windows.net'

然后创建CREATE EXTERNAL TABLE，因为我们已经设置了容器，所以只需要像下面这样直接在LOCATION中设置/folder/filename(如果'store17'是容器名称):

CREATE EXTERNAL TABLE [dbo].[exttest1]
( [description] [nvarchar](4000) NULL,
    [industry] [nvarchar](4000) NULL,
    [level] [nvarchar](4000) NULL,
    [size] [nvarchar](4000) NULL,
    [line_code] [nvarchar](4000) NULL,
    [value] [nvarchar](4000) NULL )
WITH
(
    LOCATION='/test2/SampleData.csv' , --/folder/file
    DATA_SOURCE = ds1 ,
    FILE_FORMAT = FF1 ,
    REJECT_TYPE = VALUE ,
    REJECT_VALUE = 0
) ;

但是从你的第二个屏幕截图来看，为什么 lLOCATION 更改为 /test2/？

这是完整的代码示例，我将 dbotest.csv 加载到我的 ADW，经过测试并且运行良好:

CSV 文件:

T-SQL 代码:

CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH
  IDENTITY = '****', --Storage account name
  SECRET = '*****' ;--accournt key

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH
  ( LOCATION = 'wasbs://testcontainer@****.blob.core.windows.net/' ,--
    CREDENTIAL = AzureStorageCredential ,
    TYPE = HADOOP 
  ) ;

  --Create external file format
CREATE EXTERNAL FILE FORMAT TextFileFormat
WITH (FORMAT_TYPE = DELIMITEDTEXT,
      FORMAT_OPTIONS(
          FIELD_TERMINATOR = ',',
          STRING_DELIMITER = '"',
          USE_TYPE_DEFAULT = True)
)

---Create external table wiht format
  CREATE EXTERNAL TABLE [dbo].[dbotest]
( [id] int,
  [name] nvarchar(50)
  )
WITH
(
    LOCATION='/csv/dbotest.csv' ,--/folder/filename
    DATA_SOURCE = MyAzureStorage ,
    FILE_FORMAT = TextFileFormat ,
    REJECT_TYPE = VALUE ,
    REJECT_VALUE = 0
) ;

数据检查:

希望这有帮助。