Azure SQL Server外部表 [英] Azure SQL Server External Table

问题描述

你好，

关于在Azure SQL Server数据库中创建外部表以访问blob文件，我有几个问题.

I have couple of question regarding creating External table in Azure SQL Server database to access blob file.

1)我们能否通过Polybase从SQL Server外部表访问Azure blob中的CSV文件

1) Can we access CSV file in Azure blob from SQL Server External table through Polybase

2)如果是，那么我们可以使用下面的查询来创建外部文件格式

2) If yes then can we use below query to create External File format

创建外部文件格式TextfileFormat WITH(FORMAT_TYPE = DelimitedText，

FORMAT_OPTIONS(FIELD_TERMINATOR = N'，'，USE_TYPE_DEFAULT = True))

FORMAT_OPTIONS (FIELD_TERMINATOR = N',', USE_TYPE_DEFAULT = True))

3)我们是否必须在SQL Server实例中启用Polybase功能才能在SQL Server数据库2017/2016中创建外部表

3) Do we have to enable Polybase feature in SQL Server instance to create External table in SQL Server database 2017/2016

4)可以在Azure SQL Server数据库上创建外部表还是只能在Azure SQL Server数据仓库中创建

4) Can External table be created on Azure SQL Server database or only in Azure SQL Server Data warehouse

5)我正在使用未启用/安装Polybase的Azure SQL Server数据库，但是我能够成功执行以下查询

5) I am using Azure SQL Server Database where Polybase is not enabled/Installed but I am able to execute below query successfully 

创建外部数据源AzureBlob 
WITH(  
TYPE = BLOB_STORAGE，  
LOCATION ='wasbs://[e-mail]'，
CREDENTIAL = AzureStorageCredential

CREATE EXTERNAL DATA SOURCE AzureBlob  
WITH (  
TYPE = BLOB_STORAGE,  
LOCATION = 'wasbs://[e-mail]', 
CREDENTIAL = AzureStorageCredential

); 

但是当我使用HADOOP类型而不是BLOB_STORAGE时出现错误，不确定为什么可以让我知道.

However I get error when I use Type HADOOP instead of BLOB_STORAGE, Not sure why Can you please let me know.

6)创建文件格式时出现以下错误

6) I am getting below error while creating File Format

"EXTERNAL"附近的语法不正确.

Incorrect syntax near 'EXTERNAL'.

查询为:

使用(FORMAT_TYPE = DelimitedText，
FORMAT_OPTIONS(FIELD_TERMINATOR = N'，'，USE_TYPE_DEFAULT = True))

Create EXTERNAL FILE FORMAT TextfileFormat WITH (FORMAT_TYPE = DelimitedText,
FORMAT_OPTIONS (FIELD_TERMINATOR = N',', USE_TYPE_DEFAULT = True))

如何修复?

在创建外部表以及如何解决上述问题之前，您能帮我做什么吗?

Can you please help me what to do before creating External table and how to fix above Issues.

非常感谢！

此致

马赫什语

推荐答案

从文档https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-2017
为PolyBase创建的外部表不能用于弹性数据库查询.同样，为Elastic Database查询创建的外部表不能用于PolyBase等.
PolyBase仅在SQL Server 2016(或更高版本)，Azure SQL数据仓库和并行数据仓库上受支持.弹性数据库查询仅在Azure SQL数据库v12或更高版本上受支持.

From the documentation https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-2017
An external table created for PolyBase cannot be used for Elastic Database queries. Similarly, an external table created for Elastic Database queries cannot be used for PolyBase, etc.
PolyBase is supported only on SQL Server 2016 (or higher), Azure SQL Data Warehouse, and Parallel Data Warehouse. Elastic Database queries are supported only on Azure SQL Database v12 or later.

关于您的问题:
1)不，Azure SQL数据库不支持Polybase.您可以在此处投票添加支持: https://feedback.azure.com/forums/217321-sql-database/suggestions/17376250-polybase-support-for-azure-sql-database
2)Azure SQL数据库不支持创建外部文件格式，您可以在文档https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-external-file-format中确认-transact-sql?view = sql-server-2017

3)在SQL Server中，实例必须安装此功能.

4)在这两者上，但在Azure SQL数据库上，仅适用于Elastic Database查询使用跨Azure SQL数据库查询的含义. https://docs.microsoft.com/zh-cn/azure/sql-database/sql-database-elastic-query-overview#vertical-partitioning---跨数据库查询

5)Azure SQL数据库不支持HADOOP，因为它不支持Polybase.

6)如答案2)Azure SQL数据库不支持创建外部文件格式

Regarding your questions:
1) No, Azure SQL Database don't support Polybase. You can vote to add support here: https://feedback.azure.com/forums/217321-sql-database/suggestions/17376250-polybase-support-for-azure-sql-database
2) Create External File Format is not supported on Azure SQL Database as you can confirm in the documentation, https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql?view=sql-server-2017

3) In SQL Server the instance has to have this feature installed.

4) On both, but on Azure SQL Database it's only for Elastic Database queries use meaning cross Azure SQL Databases queries. https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-overview#vertical-partitioning---cross-database-queries

5) Azure SQL Database don't support HADOOP has it doesn't support Polybase.

6) As in answer 2) Create External File Format is not supported in Azure SQL Database

如果主要目标是将数据从Azure存储加载到Azure SQL数据库，则可以尝试批量插入或OpenRowSet.
此处的更多信息:https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/02/23/loading-files-from-azure-blob-storage-into-azure-sql-database/
https://docs.microsoft.com/zh-cn/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017
https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/03/24/lesson-learned-34-does-azure-sql-database-support-openjson/

If the main goal is to load data from Azure Storage into Azure SQL Database you can try Bulk Insert or OpenRowSet.
More information on this here: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/02/23/loading-files-from-azure-blob-storage-into-azure-sql-database/
https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017
https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/03/24/lesson-learned-34-does-azure-sql-database-support-openjson/

这篇关于Azure SQL Server外部表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！