通过Blob存储在Azure SQL数据库中创建表 [英] Create a table in Azure SQL Database from Blob Storage

问题描述

我需要从我们的Azure数据仓库中取出大表，并将它们移到独立的Azure SQL数据库中.我无法使数据工厂足够快地工作以适应我的情况.我可以通过外部表将表从数据仓库中放入Blob存储中.我不知道如何在Azure SQL数据库上创建外部表，并将外部数据源添加到我的Blob存储.

I need to take large tables out of our Azure Data Warehouse and move them over to stand alone Azure SQL Databases. I haven't been able to get the Data Factory to work quickly enough for my scenario. I can get my tables into Blob storage from my Data Warehouse via external tables. What I can not figure out is how to create an external table on an Azure SQL Database with an external data source to my Blob storage.

这是用于将我的表放入Blob存储中的格式文件，外部数据源和外部表:

This is the format file, external data source, and external table used to get my table into blob storage:

CREATE EXTERNAL FILE FORMAT [DelimitedText] 
WITH (
    FORMAT_TYPE = DELIMITEDTEXT, 
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = N'~¶~', 
        USE_TYPE_DEFAULT = False
    ), 
    DATA_COMPRESSION = N'org.apache.hadoop.io.compress.GzipCodec')
GO

CREATE EXTERNAL DATA SOURCE [myDataSource] 
WITH (
    TYPE = HADOOP, 
    LOCATION = N'wasbs://<blob container>@<storage account>.blob.core.windows.net', 
    CREDENTIAL = [myCredential])
GO

CREATE EXTERNAL TABLE [dbo].[myTable] 
WITH (
    DATA_SOURCE = [myDataSource] ,
    LOCATION = N'MY_FOLDER/',
    FILE_FORMAT = [DelimitedText]
)
AS
SELECT *
FROM dbo.mytable

我可以在Azure SQL数据库中创建的唯一外部数据源是 TYPE = SHARD_MAP_MANAGER ，对吗?该链接看起来像我应该能够使用 TYPE = HADOOP 创建外部数据源，但是出现"EXTERNAL附近的错误"错误.我也无法创建外部文件格式.在Azure SQL数据库中有可能吗?

The only external data source I'm able to create in the Azure SQL Database is of TYPE=SHARD_MAP_MANAGER is that right or necessary? This link looks like I should be able to create an external data source using TYPE=HADOOP but I get an "error near EXTERNAL" error. I'm also unable to create an EXTERNAL FILE FORMAT. Is that possible in Azure SQL Database?

https://msdn.microsoft.com/zh-us/library/dn935022.aspx#示例:Azure SQL数据库

最终，我试图为我的Blob存储创建一个外部表，然后从该Blob插入我的Azure SQL数据库中的表.然后放下容器.

Ultimately, I'm trying to create an external table to my blob storage and then insert into a table in my Azure SQL Database from that blob. Then drop the container.

推荐答案

仅在本地 SQL中无法在 Azure SQL数据库上使用PolyBase功能.Server 2016数据库.

It is not possible to use PolyBase features on Azure SQL Database, only in on-premise SQL Server 2016 databases.

在该文章有一个注释:

PolyBase仅在SQL Server 2016，Azure SQL数据仓库和并行数据仓库上受支持.弹性数据库查询仅在Azure SQL数据库v12或更高版本上受支持.

PolyBase is supported only on SQL Server 2016, Azure SQL Data Warehouse, and Parallel Data Warehouse. Elastic Database queries are supported only on Azure SQL Database v12 or later.

相反，您可以创建 Azure SQL数据仓库(如果需要，请在同一Azure SQL Server上).如果您运行该指南，它将为您工作.它不适用于Hadoop( https://msdn.microsoft.com/en-us/library/mt703314.aspx )，但是据我所知，您是从Azure blob存储导入的，并且可以在Azure SQL数据仓库上使用.

Instead you could create an Azure SQL Data Warehouse (on the same Azure SQL Server if you wish). The guide will work for you if you run on that instead. It will not work for Hadoop (https://msdn.microsoft.com/en-us/library/mt703314.aspx), but as I understand your question you are importing from an Azure blob storage, and that will work on Azure SQL data warehouse.

这篇关于通过Blob存储在Azure SQL数据库中创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！