Polybase连接本地CSV文件

Question

我无法从SQL Server 2019 Polybase访问本地CSV文件。这是一个简单的3列文本文件。我还创建了一个本地系统DSN (从ODBC32 UI)。

我从here得到了示例代码。但是，链接中的驱动程序(cdata)不是免费的。任何帮助解决这个问题的人都将不胜感激。

create master key encryption by password = 'Polybase2CSV';

create database scoped credential csv_creds
with identity = 'username', secret = 'password';

create external data source csv_source
with ( 
  location = 'odbc://localhost',
  connection_options = 'DSN=CustomerDSN', -- this is the DSN name 
  -- PUSHDOWN = ON | OFF,
  credential = csv_creds
);


CREATE EXTERNAL TABLE Customer
(
    CUSTOMERID int,
    CUSTOMERNAME varchar(250),
    DEPARTMENT varchar(250)
) WITH (
    LOCATION='customer.txt',
    DATA_SOURCE=csv_source
);

Answer 1

这需要几个步骤才能使其成功工作。作为先决条件，您需要确保SQL Server 2019已更新到CU4 (KB4548597)以修复一些已知问题。对于免费的解决方案，您需要安装64位版本的Microsoft Access Database Engine 2016 Redistributable。这将安装64位版本的ODBC驱动程序。

有了这两件事，您现在就可以创建外部数据源了。我建议禁用PUSHDOWN。我看到它会导致这个特定的驱动程序出现一些问题。

如果要直接连接到包含标题行的CSV文件，只需指定Access文本驱动程序和将包含文件的文件夹即可创建外部数据源：

CREATE EXTERNAL DATA SOURCE MyODBC
WITH 
( 
    LOCATION = 'odbc://localhost',
    CONNECTION_OPTIONS = 'Driver=Microsoft Access Text Driver (*.txt, *.csv);Dbq=F:\data\files\',
    PUSHDOWN = OFF
);

要使用数据源，需要创建一个反映文件格式的外部表格定义。LOCATION参数将是要加载的文件的名称。您可以将文件名和驱动程序名括在大括号中，以避免使用特殊字符的问题。确保为该表定义的列名与标题行中的名称相匹配，这一点很重要。因为您使用的是CU4，所以如果数据类型与驱动程序的预期不符，您将得到一个错误，指出预期的数据类型。

CREATE EXTERNAL TABLE dbo.CsvData
(
    Name nvarchar(128),
    Count int,
    Description nvarchar(255)
)
WITH
(
    LOCATION='[filename.csv]',
    DATA_SOURCE = [MyODBC]
)

如果要在Microsoft数据源(64位) UI中定义列名、数据类型等，请选择 Access文本驱动程序。然后可以选择文件夹、文件类型和文本文件格式的定义。确保使用64位数据源。一旦您完成了格式细节的定义，您将看到在包含这些细节的文件夹中创建了一个schema.ini文件。

对于外部数据源，您将指定DSN的名称：

CREATE EXTERNAL DATA SOURCE MyODBC
WITH 
( 
    LOCATION = 'odbc://localhost',
    CONNECTION_OPTIONS = 'DSN=LocalCSV',
    PUSHDOWN = OFF
);

EXTERNAL TABLE的创建方式与前面相同，列名和数据类型与您在DSN中声明的定义相匹配。

Answer 2

要直接创建数据源，您需要购买该驱动程序。这是选项1，但因为它不在窗口中。你还有两个选择。将该数据直接导入SQL Server，或者如果您确实想要使用PolyBase。将该数据加载到Staging SQL表中，然后创建引用该staging表的外部表。

我的假设是: CSV数据不会过时。结构/模式将保持不变。创建临时表。使用

Import-DbaCSV -Path "D:\CustomerTest\Customer.csv"`
              -SqlInstance ServerName`
              -Database DBName`
              -Table "Customer"

然后回收您的代码以连接到PolyBase或直接使用数据。

CREATE DATABASE SCOPED CREDENTIAL csv_creds
WITH IDENTITY = 'username', SECRET = 'password';

CREATE EXTERNAL DATA SOURCE csv_source
    WITH ( LOCATION = 'sqlserver://SERVERNAME:PORTNUMBER',
    PUSHDOWN = ON,
    CREDENTIAL = csv_creds);

然后，您可以根据需要定期运行PS函数将数据加载到表中。