COPY INTO (Transact-SQL)

适用于:Azure Synapse Analytics

本文介绍如何在 Azure Synapse Analytic 中使用 COPY 语句从外部存储帐户加载数据。 COPY 语句为 Azure Synapse Analytic 中的高吞吐量数据引入提供了最大的灵活性。

注意

对于 Microsoft Fabric 中的仓库,请访问 COPY INTO

使用 COPY 可以实现以下功能:

  • 权限较低的用户在加载时,不需要对数据仓库有严格的控制权限
  • 执行单个 T-SQL 语句,不需要创建任何其他数据库对象
  • 正确分析和加载 CSV 文件,其中分隔符(字符串、字段、行)在字符串分隔列中进行转义
  • 指定更精细的权限模型,无需使用共享访问签名 (SAS) 来公开存储帐户密钥
  • 为 ERRORFILE 位置 (REJECTED_ROW_LOCATION) 使用一个不同的存储帐户
  • 为每个目标列自定义默认值,并指定要加载到特定目标列中的源数据字段
  • 指定 CSV 文件的自定义行终止符、字段终止符和字段引号
  • 对 CSV 文件使用 SQL Server 日期格式
  • 在存储位置路径中指定通配符和多个文件
  • 自动架构发现简化了定义源数据并将其映射到目标表的过程
  • 自动表创建过程会自动创建表,并与自动架构发现一起工作
  • 直接将 Parquet 文件中的复杂数据类型(如地图和列表)加载到字符串列中,而无需使用其他工具来预处理数据

注意

若要从 Parquet 文件加载复杂数据类型,必须使用 <a0/> 自动创建表。

请访问以下文档,了解使用 COPY 语句的综合示例和快速入门:

注意

Microsoft Entra ID 以前称为 Azure Active Directory (Azure AD)。

语法

COPY INTO [ schema. ] table_name
[ (Column_list) ]
FROM '<external_location>' [ , ...n ]
WITH
 (
 [ FILE_TYPE = { 'CSV' | 'PARQUET' | 'ORC' } ]
 [ , FILE_FORMAT = EXTERNAL FILE FORMAT OBJECT ]
 [ , CREDENTIAL = (AZURE CREDENTIAL) ]
 [ , ERRORFILE = ' [ http(s)://storageaccount/container ] /errorfile_directory [ / ] ] '
 [ , ERRORFILE_CREDENTIAL = (AZURE CREDENTIAL) ]
 [ , MAXERRORS = max_errors ]
 [ , COMPRESSION = { 'Gzip' | 'DefaultCodec' | 'Snappy' } ]
 [ , FIELDQUOTE = 'string_delimiter' ]
 [ , FIELDTERMINATOR =  'field_terminator' ]
 [ , ROWTERMINATOR = 'row_terminator' ]
 [ , FIRSTROW = first_row ]
 [ , DATEFORMAT = 'date_format' ]
 [ , ENCODING = { 'UTF8' | 'UTF16' } ]
 [ , IDENTITY_INSERT = { 'ON' | 'OFF' } ]
 [ , AUTO_CREATE_TABLE = { 'ON' | 'OFF' } ]
)

参数

schema_name

如果执行操作的用户的默认架构是指定表的架构,则为可选。 如果未 指定架构 ,并且执行 COPY 操作的用户的默认架构与指定表的架构不同,则取消 COPY,并返回错误消息。

table_name

要将数据复制到其中的表的名称。 目标表可以是临时或永久表,并且必须已存在于数据库中。 对于自动架构检测模式,请不要提供列列表。

(column_list)

包含一列或多列的可选列表,用于将源数据字段映射到目标表列以加载数据。

AUTO_CREATE_TABLE = 'ON' 情况下,请勿指定 column_list。

必须用括号将 column_list 括起来,并且用逗号进行分隔。 列列表的格式如下:

[(Column_name [default Default_value] [Field_number] [,...n])]

  • Column_name:目标表中列的名称。
  • Default_value - 替换输入文件中的任何 NULL 值的默认值。 默认值适用于所有文件格式。 如果省略列列表中的某一列或者某个输入文件字段为空,则 COPY 会尝试从输入文件中加载 NULL。 默认值在关键字“default”前面
  • Field_number - 映射到目标列的输入文件字段编号。
  • 字段索引从 1 开始。

如果未指定列列表,则 COPY 将根据源和目标顺序映射列:输入字段 1 映射到目标列 1,字段 2 映射到列 2,依此类推。

外部位置

包含数据的文件的暂存位置。 目前支持 Azure Data Lake Storage (ADLS) Gen2 和 Azure Blob 存储:

  • Blob 存储的外部位置:https://<account\>.blob.core.windows.net/<container\>/<path\>
  • ADLS Gen2 的外部位置:https://<account\>.dfs.core.windows.net/<container\>/<path\>

注意

.blob 终结点也可用于 ADLS Gen2,并且当前可获得最佳性能。 当身份验证方法不需要 .dfs 时,请使用 .blob 终结点。

  • Account:存储帐户名称

  • Container:blob 容器名称

  • Path:数据的文件夹或文件路径。 位置从容器开始。 如果指定了文件夹,则 COPY 将从该文件夹及其所有子文件夹中检索所有文件。 除非在路径中显式指定,否则 COPY 会忽略隐藏文件夹,并且不返回以下划线 (_) 或句点 (.) 开头的文件。 即使使用通配符指定路径也是如此。

可以在路径中使用通配符:

  • 通配符路径名称匹配区分大小写
  • 可以使用反斜杠字符 (\) 对通配符进行转义
  • 通配符扩展以递归方式应用。 例如,在下面的示例中,将加载 Customer1(包括 Customer1 的子目录)下的所有 CSV 文件:Account/Container/Customer1/*.csv

注意

为了获得最佳性能,请避免指定通配符,因为通配符会扩展成更多文件。 如有可能,请列出多个文件位置,而不是指定通配符。

只能通过逗号分隔列表从同一存储帐户和容器中指定多个文件位置,例如:

  • https://<account>.blob.core.windows.net/<container\>/<path\>, https://<account\>.blob.core.windows.net/<container\>/<path\>

FILE_TYPE = { 'CSV' | 'PARQUET' | 'ORC' }

FILE_TYPE 指定外部数据的格式。

  • CSV:指定符合 RFC 4180 标准的逗号分隔值文件。
  • PARQUET:指定 Parquet 格式。
  • ORC:指定优化行纵栏表 (ORC) 格式。

注意

PolyBase 中的文件类型“分隔文本”被替换为“CSV”文件格式,后者可以通过 FIELDTERMINATOR 参数配置默认逗号分隔符。

FILE_FORMAT = external_file_format_name

FILE_FORMAT 仅适用于 Parquet 和 ORC 文件,用于指定为外部数据存储文件类型和压缩方法的外部文件格式对象的名称。 若要创建外部文件格式,请使用 CREATE EXTERNAL FILE FORMAT

CREDENTIAL (IDENTITY = '', SECRET = '')

CREDENTIAL 指定访问外部存储帐户的身份验证机制。 身份验证方法包括:

CSV Parquet ORC
Azure Blob 存储 SAS/MSI/SERVICE PRINCIPAL/KEY/AAD SAS/KEY SAS/KEY
Azure Data Lake Gen2 SAS/MSI/SERVICE PRINCIPAL/KEY/AAD SAS (blob 1 )/MSI (dfs 2 )/SERVICE PRINCIPAL/KEY/AAD SAS (blob 1 )/MSI (dfs 2 )/SERVICE PRINCIPAL/KEY/AAD

1:此身份验证方法需要在外部位置路径使用 .blob 终结点 (.blob.core.windows.net)。

2:此身份验证方法需要在外部位置路径使用 .dfs 终结点 (.dfs.core.windows.net)。

注意

  • 使用 Microsoft Entra ID 或公共存储帐户进行身份验证时,无需指定 CREDENTIAL。
  • 如果存储帐户与 VNet 相关联,则必须使用托管标识进行身份验证。
  • 使用共享访问签名 (SAS) 进行身份验证

    • IDENTITY:一个值为“共享访问签名”的常量
    • SECRET:共享访问签名提供对存储帐户中资源的委派访问权限。
    • 所需的最低权限:READ 和 LIST
  • 使用服务主体进行身份验证

    • IDENTITY:<ClientID>@<OAuth_2.0_Token_EndPoint>
    • SECRET:Microsoft Entra 应用程序服务主体密钥
    • 所需的最小 RBAC 角色:存储 blob 数据参与者、存储 blob 数据所有者或存储 blob 数据读取者
  • 使用存储帐户密钥进行身份验证

    • IDENTITY:一个值为“存储帐户密钥”的常量
    • SECRET:存储帐户密钥
  • 使用托管标识(VNet 服务终结点)进行身份验证

    • IDENTITY:一个值为“托管标识”的常量
    • 所需的最低 RBAC 角色:在 Azure 中注册的 Microsoft Entra 逻辑服务器的存储 blob 数据参与者或存储 blob 数据所有者。 使用未与 Synapse 工作区关联的专用 SQL 池(以前为 SQL DW)时,不需要此 RBAC 角色,但托管标识需要对目标对象具有访问控制列表(ACL)权限才能启用对源文件的读取访问权限
  • 使用 Microsoft Entra 用户进行身份验证

    • 不需要 CREDENTIAL
    • 所需的最低 RBAC 角色:Microsoft Entra 用户的存储 blob 数据参与者或存储 blob 数据所有者

ERRORFILE = Directory Location

ERRORFILE 仅适用于 CSV。 指定 COPY 语句中的目录,应在该目录中写入被拒绝的行和相应的错误文件。 可以指定存储帐户的完整路径,也可以指定容器的相对路径。 如果指定的路径不存在,系统将代你创建一个。 创建名称为“_rejectedrows”的子目录。 除非在位置参数中明确命名,否则,“_”字符将确保对该目录转义以进行其他数据处理。

注意

将相对路径传递给 ERRORFILE 时,该路径相对于external_location中指定的容器路径。

在此目录中,存在根据负荷提交时间创建的文件夹,采用 YearMonthDay-HourMinuteSecond 格式(例如,20180330-173205)。 在此文件夹中,将写入两种类型的文件,即原因(错误)文件和数据(行)文件,每个文件都预先追加 queryID、distributionID 和文件 GUID。 数据和原因位于不同的文件中,因此相应的文件具有匹配的前缀。

如果 ERRORFILE 定义了存储帐户的完整路径,则会使用 ERRORFILE_CREDENTIAL 连接到该存储。 否则,将使用为 CREDENTIAL 指定的值。 当用于源数据的相同凭据用于 ERRORFILE 时,适用于ERRORFILE_CREDENTIAL的限制也适用

ERRORFILE_CREDENTIAL = (IDENTITY= '', SECRET = '')

ERRORFILE_CREDENTIAL 仅适用于 CSV 文件。 支持的数据源和身份验证方法包括:

  • Azure Blob 存储 - SAS/服务主体/AAD

  • Azure Data Lake Gen2 - SAS/MSI/SERVICE PRINCIPAL/AAD

  • 使用共享访问签名 (SAS) 进行身份验证

    • IDENTITY:一个值为“共享访问签名”的常量
    • SECRET:共享访问签名提供对存储帐户中资源的委派访问权限。
    • 所需的最低权限:READ、LIST、WRITE、CREATE、DELETE
  • 使用服务主体进行身份验证

    • IDENTITY:<ClientID>@<OAuth_2.0_Token_EndPoint>
    • SECRET:Microsoft Entra 应用程序服务主体密钥
    • 所需的最小 RBAC 角色:存储 blob 数据参与者或存储 blob 数据所有者

注意

使用 OAuth 2.0 令牌终结点 V1

  • 使用托管标识(VNet 服务终结点)进行身份验证

    • IDENTITY:一个值为“托管标识”的常量
    • 所需的最低 RBAC 角色:Microsoft Entra 已注册SQL 数据库服务器的存储 blob 数据参与者或存储 blob 数据所有者
  • 使用 Microsoft Entra 用户进行身份验证

    • 不需要 CREDENTIAL
    • 所需的最低 RBAC 角色:Microsoft Entra 用户的存储 blob 数据参与者或存储 blob 数据所有者

不支持将存储帐户密钥用于ERRORFILE_CREDENTIAL。

注意

如果为 ERRORFILE 使用相同的存储帐户,并指定相对于容器根目录的 ERRORFILE 路径,则无需指定 ERROR_CREDENTIAL。

MAXERRORS = max_errors

MAXERRORS 指定 COPY 操作失败之前加载中允许的最大拒绝行数。 COPY 操作无法导入的每一行都将被忽略并计为一个错误。 如果未指定 max_errors,则默认值为 0。

MAXERRORS 不能与AUTO_CREATE_TABLE一起使用。

当FILE_TYPE为“PARQUET”时,数据类型转换错误(例如 Parquet 二进制文件到 SQL 整数)导致的异常仍会导致 COPY INTO 失败,忽略 MAXERRORS

COMPRESSION = { 'DefaultCodec ' | 'Snappy' | 'GZIP' | 'NONE'}

COMPRESSION 是一个可选参数,用于指定外部数据的数据压缩方法。

  • CSV 支持 GZIP
  • Parquet 支持 GZIP 和 Snappy
  • ORC 支持 DefaultCodec 和 Snappy。
  • Zlib 是 ORC 的默认压缩方法

如果未指定此参数,COPY 命令将根据文件扩展名自动检测压缩类型:

  • .gz - GZIP
  • .snappy - Snappy
  • .deflate - DefaultCodec(仅限 Parquet 和 ORC)

COPY 命令要求 gzip 文件不包含任何尾随垃圾才能正常运行。 gzip 格式严格要求文件由有效成员组成,而无需在文件之前、之间或之后提供任何其他信息。 与此格式的任何偏差(例如存在尾随非 gzip 数据)将导致 COPY 命令失败。 确保 gzip 文件末尾没有尾随垃圾,以确保 COPY 可以成功处理这些文件。

FIELDQUOTE = 'field_quote'

FIELDQUOTE 适用于 CSV,它指定一个字符,该字符用作 CSV 文件中的引号字符(字符串分隔符)。 如果未指定,根据 RFC 4180 标准中的定义,引号字符 (") 将用作引号字符。 FIELDQUOTE 也支持十六进制表示法。 对于 FIELDQUOTE,UTF-8 不支持扩展 ASCII 和多字节字符。

注意

FIELDQUOTE 字符会在有双 FIELDQUOTE(分隔符)的字符串列中进行转义。

FIELDTERMINATOR = 'field_terminator'

FIELDTERMINATOR 仅适用于 CSV。 指定在 CSV 文件中使用的字段终止符。 可使用十六进制表示法指定字段终止符。 字段终止符可以是多字符。 默认的字段终止符为 (,)。 对于 FIELDTERMINATOR,UTF-8 不支持扩展 ASCII 和多字节字符。

ROW TERMINATOR = 'row_terminator'

ROW TERMINATOR 仅适用于 CSV。 指定在 CSV 文件中使用的行终止符。 可使用十六进制表示法指定行终止符。 行终止符可以是多字符。 默认情况下,行终止符为 \r\n

当指定 \n(换行符)以生成 \r\n 时,COPY 命令会为 \r 字符加上前缀。 要仅指定 \n 字符,请使用十六进制表示法 (0x0A)。 以十六进制指定多字符行终止符时,请勿在每个字符之间指定 0x。

行终止符的 UTF-8 不支持扩展 ASCII 和多字节字符。

FIRSTROW = First_row_int

FIRSTROW 适用于 CSV,它为 COPY 命令指定在所有文件中最先读取的行号。 值从 1 开始,1 是默认值。 如果值设置为二,则在加载数据时,会跳过每个文件中的第一行(标头行)。 如果有行终止符,则跳过该行。

DATEFORMAT = { 'mdy' | 'dmy' | 'ymd' | 'ydm' | 'myd' | 'dym' }

DATEFORMAT 仅适用于 CSV,它指定映射到 SQL Server 日期格式的日期格式。 有关所有 Transact-SQL 日期和时间数据类型及函数的概述,请参阅日期和时间数据类型及函数 (Transact-SQL)。 COPY 命令中的 DATEFORMAT 优先于在会话级别配置的 DATEFORMAT

ENCODING = 'UTF8' | 'UTF16'

ENCODING 仅适用于 CSV。 默认值为 UTF8。 指定 COPY 命令加载的文件的数据编码标准。

IDENTITY_INSERT = 'ON' | 'OFF'

IDENTITY_INSERT 指定是否将导入数据文件中的标识值用于标识列。 如果 IDENTITY_INSERT 为 OFF(默认值),则验证此列的标识值,但不导入。 Azure Synapse Analytics 会根据创建表时指定的种子和增量值自动分配唯一值。 请注意 COPY 命令的以下行为:

  • 如果IDENTITY_INSERT为 OFF,并且表具有标识列
    • 必须指定一个不会将输入字段映射到标识列的列列表。
  • 如果IDENTITY_INSERT为 ON,并且表具有标识列
    • 如果传递了列列表,则它必须将输入字段映射到标识列。
  • 列列表中的 IDENTITY COLUMN 不支持默认值。
  • 一次只能为一个表设置 IDENTITY_INSERT。

AUTO_CREATE_TABLE = { 'ON' | 'OFF' }

AUTO_CREATE_TABLE 指定是否可以通过与自动架构发现一起操作来自动创建表。 它仅适用于 Parquet 文件。

  • ON:启用自动表创建。 COPY INTO 进程将通过发现要加载的文件的结构,自动创建新表。 还可以与预先存在的表一起使用,以利用 Parquet 文件的自动架构发现功能。
  • OFF:未启用自动表创建。 默认。

注意

自动表创建与自动架构发现一起工作。 默认不启用自动表创建。

不要使用 COPY INTO 和 AUTO_CREATE_TABLE = 'ON' 从 Parquet 文件加载到哈希分布式表中。

如果要使用 COPY INTO 将 Parquet 文件加载到哈希分布式表中,请将其加载到轮循机制临时表中,然后是从该表 INSERT ... SELECT 到目标哈希分布式表。

权限

执行 COPY 命令的用户必须具有以下权限:

需要 INSERT 和 ADMINISTER BULK OPERATIONS 权限。 在 Azure Synapse Analytics中,需要 INSERT 和 ADMINISTER DATABASE BULK OPERATIONS 权限。

此外,如果执行 COPY 命令的用户还打算生成新表并将数据加载到其中,则需要 CREATE TABLE 和 ALTER ON SCHEMA 权限。

例如,若要允许 mike@contoso.com 使用 COPY 在 HR 架构中创建新表,并插入 Parquet 文件中的数据,请使用以下 Transact-SQL 示例:

GRANT ADMINISTER DATABASE BULK OPERATIONS to [mike@contoso.com];
GRANT INSERT to [mike@contoso.com];

GRANT CREATE TABLE to [mike@contoso.com];
GRANT ALTER on SCHEMA::HR to [mike@contoso.com];

备注

COPY 语句仅接受行数据和命令参数的 UTF-8 和 UTF-16 有效字符。 使用无效字符的源文件或参数(如 ROW TERMINATOR 或 FIELD TERMINATOR)可能会被 COPY 语句错误解释,并导致意外结果,例如数据损坏或其他故障。 在调用 COPY 语句之前,请确保源文件和参数符合 UTF-8 或 UTF-16。

示例

A. 从公共存储帐户加载数据

下面的示例将从公共存储帐户加载数据,这是 COPY 命令最简单的一种形式。 在此示例中,COPY 语句的默认值与行项 csv 文件的格式匹配。

COPY INTO dbo.[lineitem]
FROM 'https://unsecureaccount.blob.core.windows.net/customerdatasets/folder1/lineitem.csv'
WITH (FIELDTERMINATOR = '|')

COPY 命令的默认值为:

  • DATEFORMAT = Session DATEFORMAT

  • MAXERRORS = 0

  • COMPRESSION 默认值为未压缩

  • FIELDQUOTE = '“'

  • FIELDTERMINATOR = ','

  • ROWTERMINATOR = '\n'

重要

COPY 在内部将 \n 视为 \r\n。 有关详细信息,请参阅 ROWTERMINATOR 部分。

  • FIRSTROW = 1

  • ENCODING = 'UTF8'

  • FILE_TYPE = 'CSV'

  • IDENTITY_INSERT = 'OFF'

B. 加载时通过共享访问签名 (SAS) 进行身份验证

下面的示例将加载使用换行符作为行终止符的文件(例如 UNIX 输出)。 此示例还使用 SAS 密钥向 Azure Blob 存储进行身份验证。

COPY INTO test_1
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='<Your_SAS_Token>'),
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='?sv=2018-03-28&ss=bfqt&srt=sco&sp=rl&st=2016-10-17T20%3A14%3A55Z&se=2021-10-18T20%3A19%3A00Z&sig=IEoOdmeYnE9%2FKiJDSHFSYsz4AkNa%2F%2BTx61FuQ%2FfKHefqoBE%3D'),
    FIELDQUOTE = '"',
    FIELDTERMINATOR=';',
    ROWTERMINATOR='0X0A',
    ENCODING = 'UTF8',
    DATEFORMAT = 'ymd',
    MAXERRORS = 10,
    ERRORFILE = '/errorsfolder',--path starting from the storage container
    IDENTITY_INSERT = 'ON'
)

C. 加载具有默认值的列列表时通过存储帐户密钥进行身份验证

此示例将加载指定了具有默认值的列列表的文件。

--Note when specifying the column list, input field numbers start from 1
COPY INTO test_1 (Col_one default 'myStringDefault' 1, Col_two default 1 3)
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<Your_Account_Key>'),
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='x6RWv4It5F2msnjelv3H4DA80n0PQW0daPdw43jM0nyetx4c6CpDkdj3986DX5AHFMIf/YN4y6kkCnU8lb+Wx0Pj+6MDw=='),
    FIELDQUOTE = '"',
    FIELDTERMINATOR=',',
    ROWTERMINATOR='0x0A',
    ENCODING = 'UTF8',
    FIRSTROW = 2
)

D. 使用现有文件格式对象加载 Parquet 或 ORC

此示例将使用通配符加载某个文件夹下的所有 Parquet 文件。

COPY INTO test_parquet
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.parquet'
WITH (
    FILE_FORMAT = myFileFormat,
    CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='<Your_SAS_Token>')
)

E. 加载时指定通配符和多个文件

COPY INTO t1
FROM
'https://myaccount.blob.core.windows.net/myblobcontainer/folder0/*.txt',
    'https://myaccount.blob.core.windows.net/myblobcontainer/folder1'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL=(IDENTITY= '<client_id>@<OAuth_2.0_Token_EndPoint>',SECRET='<key>'),
    FIELDTERMINATOR = '|'
)

F. 使用 MSI 凭据加载

COPY INTO dbo.myCOPYDemoTable
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder0/*.txt'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL = (IDENTITY = 'Managed Identity'),
    FIELDQUOTE = '"',
    FIELDTERMINATOR=','
)

G. 使用自动架构检测进行加载

COPY INTO [myCOPYDemoTable]
FROM 'https://myaccount.blob.core.windows.net/customerdatasets/folder1/lineitem.parquet'
WITH (
    FILE_TYPE = 'Parquet',
    CREDENTIAL = ( IDENTITY = 'Shared Access Signature',  SECRET='<key>'),
    AUTO_CREATE_TABLE = 'ON'
)

常见问题解答

与 PolyBase 相比,COPY 命令的性能如何?

COPY 命令具有更好的性能,具体取决于工作负载。

  • 压缩文件不能自动拆分。 为了获得最佳加载性能,请考虑在加载压缩 CSV 时将你的输入拆分为多个文件。

  • 大型未压缩 CSV 文件可以自动并行拆分和加载,因此在大多数情况下无需手动拆分未压缩的 CSV 文件。 在某些情况下,由于数据特征导致自动文件拆分不可行,手动拆分大型 CSV 仍可能提高性能。

加载压缩 CSV 文件时,COPY 命令的文件拆分指导是什么?

下表概述了文件数量指导。 一旦达到推荐的文件数量,便能获得更大的文件,性能也就越高。 文件数量为计算节点数乘以 60。 例如,在 6000DWU 中,有 12 个计算节点和 12*60 = 720 个分区。 若要获得简单的文件拆分体验,请参阅如何通过文件拆分最大化 COPY 负载吞吐量

DWU 文件数
100 60
200 60
300 60
400 60
500 60
1,000 120
1,500 180
2,000 240
2,500 300
3,000 360
5,000 600
6,000 720
7,500 900
10,000 1200
15,000 1800
30,000 3600

加载 Parquet 或 ORC 文件时,COPY 命令的文件拆分指导是什么?

不需要拆分 Parquet 或 ORC 文件,因为 COPY 命令会自动拆分这些文件。 为了获取最佳性能,Azure 存储帐户中的 Parquet 或 ORC 文件应为 256 MB 或更大。

文件的数量和大小有限制吗?

文件的数量或大小没有限制;但是,为了获得最佳性能,建议文件至少为 4 MB。

COPY 语句是否存在已知问题?

如果有在 2020 年 12 月 7 日之前创建的 Azure Synapse 工作区,则使用托管标识进行身份验证时,可能会遇到类似的错误消息:com.microsoft.sqlserver.jdbc.SQLServerException: Managed Service Identity has not been enabled on this server. Please enable Managed Service Identity and try again.

请按照以下步骤操作,通过重新注册工作区的托管标识来规避此问题:

  1. 安装 Azure PowerShell 中的说明进行操作。 请参阅安装 PowerShell
  2. 使用 PowerShell 注册工作区的托管标识:
    Connect-AzAccount
    Select-AzSubscription -SubscriptionId <subscriptionId>
    Set-AzSqlServer -ResourceGroupName your-database-server-resourceGroup -ServerName your-SQL-servername -AssignIdentity
    

适用于:Microsoft Fabric 中的仓库

本文介绍如何在 Microsoft Fabric 中的仓库中使用 COPY 语句从外部存储帐户加载数据。 COPY 语句为将高吞吐量数据引入仓库提供了最大的灵活性,也是将数据引入仓库的策略。

在 Microsoft Fabric 中,COPY (Transact-SQL) 语句当前支持 PARQUET 和 CSV 文件格式。 对于数据源,仅支持 Azure Data Lake Storage Gen2 帐户。

有关在 Microsoft Fabric 中的仓库上使用 COPY INTO 的详细信息,请参阅使用 COPY 语句将数据引入仓库

默认情况下, COPY INTO 将作为正在执行的 Entra ID 用户进行身份验证。

注意

有关 Azure Synapse Analytics,请访问 Azure Synapse Analytics 的 COPY INTO

使用 COPY 可以实现以下功能:

  • 权限较低的用户在加载时,不需要对数据仓库有严格的控制权限。
  • 执行单个 T-SQL 语句,不需要创建任何其他数据库对象。
  • 正确分析和加载 CSV 文件,其中分隔符(字符串、字段、行)在字符串分隔列中进行转义
  • 指定更精细的权限模型,无需使用共享访问签名 (SAS) 来公开存储帐户密钥。
  • 为 ERRORFILE 位置 (REJECTED_ROW_LOCATION) 使用一个不同的存储帐户。
  • 为每个目标列自定义默认值,并指定要加载到特定目标列中的源数据字段。
  • 指定 CSV 文件的自定义行终止符、字段终止符和字段引号
  • 在存储位置路径中指定通配符和多个文件。
  • 有关数据引入选项和最佳做法的详细信息,请参阅使用 COPY 语句将数据引入仓库

语法

COPY INTO [ warehouse_name. ] [ schema_name. ] table_name
[ (Column_list) ]
FROM '<external_location>' [ , ...n ]
WITH
 (
 [ FILE_TYPE = { 'CSV' | 'PARQUET' } ]
 [ , CREDENTIAL = (AZURE CREDENTIAL) ]
 [ , ERRORFILE = ' [ http(s)://storageaccount/container ] /errorfile_directory [ / ] ] '
 [ , ERRORFILE_CREDENTIAL = (AZURE CREDENTIAL) ]
 [ , MAXERRORS = max_errors ]
 [ , COMPRESSION = { 'Gzip' | 'Snappy' } ]
 [ , FIELDQUOTE = 'string_delimiter' ]
 [ , FIELDTERMINATOR =  'field_terminator' ]
 [ , ROWTERMINATOR = 'row_terminator' ]
 [ , FIRSTROW = first_row ]
 [ , ENCODING = { 'UTF8' | 'UTF16' } ]
 [ , PARSER_VERSION = { '1.0' | '2.0' } ]
)

参数

warehouse_name:

如果执行操作的用户的当前仓库是指定表的仓库,则为可选。 如果未指定仓库,并且当前仓库中不存在指定的架构和表,则 COPY 将失败,并返回错误消息。

schema_name

如果执行操作的用户的默认架构是指定表的架构,则为可选。 如果未 指定架构 ,并且执行 COPY 操作的用户的默认架构与指定表的架构不同,则取消 COPY,并返回错误消息。

table_name

要将数据复制到其中的表的名称。 目标表必须已存在于仓库中。

(column_list)

包含一列或多列的可选列表,用于将源数据字段映射到目标表列以加载数据。

必须用括号将 column_list 括起来,并且用逗号进行分隔。 列列表的格式如下:

[(Column_name [default Default_value] [Field_number] [,...n])]

  • Column_name:目标表中列的名称。
  • Default_value - 替换输入文件中的任何 NULL 值的默认值。 默认值适用于所有文件格式。 如果省略列列表中的某一列或者某个输入文件字段为空,则 COPY 会尝试从输入文件中加载 NULL。 默认值前面有关键字“default”
  • Field_number - 映射到目标列的输入文件字段编号。
  • 字段索引从 1 开始。

如果未指定 column_list,则 COPY 将根据源和目标顺序映射列:输入字段 1 映射到目标列 1,字段 2 映射到列 2,依此类推。

注意

在 Microsoft Fabric 中的仓库上使用 Parquet 文件时,列名称必须在源和目标中完全匹配。 如果目标表中的列名称与 parquet 文件中的列名称不同,则目标表列将填充 NULL。

如果未指定列列表,则 COPY 将根据源和目标顺序映射列:输入字段 1 映射到目标列 1,字段 2 映射到列 2,依此类推。

外部位置

注意

目前不支持 Fabric OneLake 路径,仅支持 BLOB 和 ADLS Gen2 存储帐户。

指定包含数据的文件的暂存位置。 目前支持 Azure Data Lake Storage (ADLS) Gen2 和 Azure Blob 存储:

  • Blob 存储的外部位置:https://<account\>.blob.core.windows.net/<container\>/<path\>
  • ADLS Gen2 的外部位置:https://<account\>.dfs.core.windows.net/<container\>/<path\>

Azure Data Lake Storage (ADLS) Gen2 提供的性能优于Azure Blob 存储(旧版)。 尽可能考虑使用 ADLS Gen2 帐户。

注意

.blob 终结点也可用于 ADLS Gen2,并且当前可获得最佳性能。 当身份验证方法不需要 .dfs 时,请使用 .blob 终结点。

  • Account:存储帐户名称

  • Container:blob 容器名称

  • Path:数据的文件夹或文件路径。 位置从容器开始。 如果指定了文件夹,则 COPY 将从该文件夹及其所有子文件夹中检索所有文件。 除非在路径中显式指定,否则 COPY 会忽略隐藏文件夹,并且不返回以下划线 (_) 或句点 (.) 开头的文件。 即使使用通配符指定路径也是如此。

可以在路径中使用通配符,其中

  • 通配符路径名称匹配区分大小写
  • 可以使用反斜杠字符 (\) 对通配符进行转义

注意

为了获得最佳性能,请避免指定通配符,因为通配符会扩展成更多文件。 如有可能,请列出多个文件位置,而不是指定通配符。

只能通过逗号分隔列表从同一存储帐户和容器中指定多个文件位置,例如:

  • https://<account>.blob.core.windows.net/<container\>/<path\>, https://<account\>.blob.core.windows.net/<container\>/<path\>

防火墙后面的外部位置

若要访问 Azure Data Lake Storage (ADLS) Gen2 上的文件以及防火墙后面的Azure Blob 存储位置,需要满足以下先决条件:

  • 必须预配托管仓库的工作区的工作区标识。 有关如何设置工作区标识的详细信息,请参阅 工作区标识
  • Entra ID 帐户必须能够使用工作区标识。
  • Entra ID 帐户必须有权通过 Azure 基于角色的访问控制(RBAC)Data Lake ACL 访问基础文件。
  • 托管仓库的 Fabric 工作区必须添加为 资源实例规则。 有关如何使用资源实例规则添加 Fabric 工作区的详细信息,请参阅 资源实例规则

FILE_TYPE = { 'CSV' | 'PARQUET' }

FILE_TYPE 指定外部数据的格式。

  • CSV:指定符合 RFC 4180 标准的逗号分隔值文件。
  • PARQUET:指定 Parquet 格式。

CREDENTIAL (IDENTITY = '', SECRET = '')

CREDENTIAL 指定访问外部存储帐户的身份验证机制。 在 Microsoft Fabric 的仓库上,唯一支持的身份验证机制是共享访问签名 (SAS) 和存储帐户密钥 (SAK)。 用户的 EntraID 身份验证是默认的,无需指定凭据。

注意

使用公共存储帐户时,无需指定 CREDENTIAL。 默认情况下,使用正在执行的用户的 Entra ID。

  • 使用共享访问签名 (SAS) 进行身份验证

    • IDENTITY:一个值为“共享访问签名”的常量
    • SECRET:共享访问签名提供对存储帐户中资源的委派访问权限。
    • 所需的最低权限:READ 和 LIST
  • 使用存储帐户密钥进行身份验证

    • IDENTITY:一个值为“存储帐户密钥”的常量
    • SECRET:存储帐户密钥

ERRORFILE = Directory Location

ERRORFILE 仅适用于 CSV。 指定目录,应在该目录中写入被拒绝的行和相应的错误文件。 可以指定存储帐户的完整路径,也可以指定容器的相对路径。 如果指定的路径不存在,系统将代你创建一个。 创建名称为“_rejectedrows”的子目录。 除非在位置参数中明确命名,否则,“_”字符将确保对该目录转义以进行其他数据处理。

注意

将相对路径传递给 ERRORFILE 时,该路径相对于external_location中指定的容器路径。

在此目录中,存在根据负荷提交时间创建的文件夹,采用 YearMonthDay-HourMinuteSecond 格式(例如,20180330-173205)。 在此文件夹中,将创建一个具有语句 ID 的文件夹,并在该文件夹下写入两种类型的文件:包含拒绝原因的 error.Json 文件,以及包含被拒绝行的 row.csv 文件。

如果 ERRORFILE 定义了存储帐户的完整路径,则会使用 ERRORFILE_CREDENTIAL 连接到该存储。 否则,将使用为 CREDENTIAL 指定的值。 当用于源数据的相同凭据用于 ERRORFILE 时,适用于ERRORFILE_CREDENTIAL的限制也适用。

ERRORFILE_CREDENTIAL = (IDENTITY= '', SECRET = '')

ERRORFILE_CREDENTIAL 仅适用于 CSV 文件。 在 Microsoft Fabric 中的仓库上,唯一支持的身份验证机制是共享访问签名 (SAS)。

  • 使用共享访问签名进行身份验证 (SAS)
    • IDENTITY:一个值为“共享访问签名”的常量
    • SECRET:共享访问签名提供对存储帐户中资源的委派访问权限。
    • 所需的最低权限:READ、LIST、WRITE、CREATE、DELETE

注意

如果为 ERRORFILE 使用相同的存储帐户,并指定相对于容器根目录的 ERRORFILE 路径,则无需指定 ERROR_CREDENTIAL。

MAXERRORS = max_errors

MAXERRORS 指定 COPY 操作失败之前加载中允许的最大拒绝行数。 COPY 操作无法导入的每一行都将被忽略并计为一个错误。 如果未指定 max_errors,则默认值为 0。

在 Microsoft Fabric 中,当FILE_TYPE为“PARQUET”时,不能使用 MAXERRORS

COMPRESSION = { 'Snappy' | 'GZIP' | 'NONE'}

COMPRESSION 是一个可选参数,用于指定外部数据的数据压缩方法。

  • CSV 支持 GZIP
  • Parquet 支持 GZIP 和 Snappy

如果未指定此参数,COPY 命令将根据文件扩展名自动检测压缩类型:

  • .gz - GZIP

当前仅PARSER_VERSION 1.0 支持加载压缩文件。

COPY 命令要求 gzip 文件不包含任何尾随垃圾才能正常运行。 gzip 格式严格要求文件由有效成员组成,而无需在文件之前、之间或之后提供任何其他信息。 与此格式的任何偏差(例如存在尾随非 gzip 数据)将导致 COPY 命令失败。 确保 gzip 文件末尾没有尾随垃圾,以确保 COPY 可以成功处理这些文件。

FIELDQUOTE = 'field_quote'

FIELDQUOTE 仅适用于 CSV。 它指定一个字符,该字符用作 CSV 文件中的引号字符(字符串分隔符)。 如果未指定,根据 RFC 4180 标准中的定义,引号字符 (") 将用作引号字符。 FIELDQUOTE 也支持十六进制表示法。 对于 FIELDQUOTE,UTF-8 不支持扩展 ASCII 和多字节字符。

注意

FIELDQUOTE 字符会在有双 FIELDQUOTE(分隔符)的字符串列中进行转义。

FIELDTERMINATOR = 'field_terminator'

FIELDTERMINATOR 仅适用于 CSV。 指定在 CSV 文件中使用的字段终止符。 还可使用十六进制表示法指定字段终止符。 字段终止符可以是多字符。 默认的字段终止符为 (,)。 对于 FIELDTERMINATOR,UTF-8 不支持扩展 ASCII 和多字节字符。

ROWTERMINATOR = 'row_terminator'

ROWTERMINATOR 仅适用于 CSV。 指定在 CSV 文件中使用的行终止符。 可使用十六进制表示法指定行终止符。 行终止符可以是多字符。 默认终止符为 \r\n\n以及 \r

当指定 \n(换行符)以生成 \r\n 时,COPY 命令会为 \r 字符加上前缀。 要仅指定 \n 字符,请使用十六进制表示法 (0x0A)。 以十六进制指定多字符行终止符时,请勿在每个字符之间指定 0x。

ROWTERMINATOR 的 UTF-8 不支持扩展 ASCII 和多字节字符。

FIRSTROW = First_row_int

FIRSTROW 仅适用于 CSV。 它为 COPY 命令指定在所有文件中最先读取的行号。 值从 1 开始,1 是默认值。 如果值设置为二,则在加载数据时,会跳过每个文件中的第一行(标头行)。 如果有行终止符,则跳过该行。

ENCODING = 'UTF8' | 'UTF16'

ENCODING 仅适用于 CSV。 默认值为 UTF8。 指定 COPY 命令加载的文件的数据编码标准。

PARSER_VERSION = { '1.0' |'2.0' }

PARSER_VERSION仅适用于 CSV。 默认值为 2.0。 指定源文件类型为 CSV 时用于引入的文件分析器。 2.0 分析程序为引入 CSV 文件提供了改进的性能。

分析器版本 2.0 具有以下限制:

  • 不支持压缩的 CSV 文件
  • 不支持使用 UTF-16 编码的文件
  • 不支持 Multicharacter 或 multibyte ROWTERMINATOR、FIELDTERMINATOR 或 FIELDQUOTE。 但是,接受“\r\n”作为默认 ROWTERMINATOR

将分析器版本 1.0 与 UTF-8 文件一起使用时,FIELDTERMINATOR 不支持多字节和多字符终止符。

分析器版本 1.0 仅适用于向后兼容性,仅当遇到这些限制时才应使用。

注意

当 COPY INTO 与采用 UTF-16 编码的压缩 CSV 文件或文件一起使用时,COPY INTO 会自动切换到 PARSER_VERSION 1.0,无需用户操作。 对于 FIELDTERMINATOR 或 ROWTERMINATOR 上的多字符终止符,COPY INTO 语句将失败。 如果需要多字符分隔符,请使用 PARSER_VERSION = “1.0”。

注解

仓库中的 COPY INTO 不允许设置用于解释日期字符串的日期格式。 默认情况下,所有日期都被视为采用年-月-日格式。 若要引入具有不同日期格式的 CSV 文件,请使用 SET DATEFORMAT 在会话级别指定所需的日期格式。 有关详细信息,请参阅 SET DATEFORMAT (Transact-SQL)

此外,COPY 语句仅接受行数据和命令参数的 UTF-8 和 UTF-16 有效字符。 使用无效字符的源文件或参数(如 ROW TERMINATOR 或 FIELD TERMINATOR)可能会被 COPY 语句错误解释,并导致意外结果,例如数据损坏或其他故障。 在调用 COPY 语句之前,请确保源文件和参数符合 UTF-8 或 UTF-16。

示例

有关在 Microsoft Fabric 中的仓库上使用 COPY INTO 的详细信息,请参阅使用 COPY 语句将数据引入仓库

A. 从公共存储帐户加载数据

下面的示例将从公共存储帐户加载数据,这是 COPY 命令最简单的一种形式。 在此示例中,COPY 语句的默认值与行项 csv 文件的格式匹配。

COPY INTO dbo.[lineitem]
FROM 'https://unsecureaccount.blob.core.windows.net/customerdatasets/folder1/lineitem.csv'

COPY 命令的默认值为:

  • MAXERRORS = 0

  • COMPRESSION 默认值为未压缩

  • FIELDQUOTE = '“'

  • FIELDTERMINATOR = ','

  • ROWTERMINATOR = '\n'

重要

COPY 在内部将 \n 视为 \r\n。 有关详细信息,请参阅 ROWTERMINATOR 部分。

  • FIRSTROW = 1

  • ENCODING = 'UTF8'

  • FILE_TYPE = 'CSV'

B. 加载时通过共享访问签名 (SAS) 进行身份验证

下面的示例将加载使用换行符作为行终止符的文件(例如 UNIX 输出)。 此示例还使用 SAS 密钥向 Azure Blob 存储进行身份验证。

COPY INTO test_1
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='<Your_SAS_Token>'),
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='?sv=2018-03-28&ss=bfqt&srt=sco&sp=rl&st=2016-10-17T20%3A14%3A55Z&se=2021-10-18T20%3A19%3A00Z&sig=IEoOdmeYnE9%2FKiJDSHFSYsz4AkNa%2F%2BTx61FuQ%2FfKHefqoBE%3D'),
    FIELDQUOTE = '"',
    FIELDTERMINATOR = ';',
    ROWTERMINATOR = '0X0A',
    ENCODING = 'UTF8',
    MAXERRORS = 10,
    ERRORFILE = '/errorsfolder'--path starting from the storage container
)

C. 加载具有默认值的列列表时通过存储帐户密钥 (SAK) 进行身份验证

此示例将加载指定了具有默认值的列列表的文件。

--Note when specifying the column list, input field numbers start from 1
COPY INTO test_1 (Col_one default 'myStringDefault' 1, Col_two default 1 3)
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<Your_account_key>'),
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='x6RWv4It5F2msnjelv3H4DA80n0PQW0daPdw43jM0nyetx4c6CpDkdj3986DX5AHFMIf/YN4y6kkCnU8lb+Wx0Pj+6MDw=='),
    FIELDQUOTE = '"',
    FIELDTERMINATOR=',',
    ROWTERMINATOR='0x0A',
    ENCODING = 'UTF8',
    FIRSTROW = 2
)

D. 加载 Parquet

此示例使用通配符通过执行用户的 EntraID 加载文件夹下的所有 Parquet 文件。

COPY INTO test_parquet
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.parquet'
WITH (
    FILE_TYPE = 'PARQUET'
)

E. 加载时指定通配符和多个文件

COPY INTO t1
FROM
'https://myaccount.blob.core.windows.net/myblobcontainer/folder0/*.txt',
    'https://myaccount.blob.core.windows.net/myblobcontainer/folder1'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='<Your_SAS_Token>')
    FIELDTERMINATOR = '|'
)

常见问题解答

加载压缩 CSV 文件时,COPY 命令的文件拆分指导是什么?

请考虑拆分大型 CSV 文件,尤其是在文件数量较小时,但为每个文件保留至少 4 MB,以提高性能。

加载 Parquet 文件时,COPY 命令的文件拆分指导是什么?

请考虑拆分大型 Parquet 文件,尤其是在文件数较小时。

文件的数量和大小有限制吗?

文件的数量或大小没有限制;但是,为了获得最佳性能,建议文件至少为 4 MB。

如果未指定凭据,将使用哪种身份验证方法?

默认情况下, COPY INTRO 将使用正在执行的用户的 Entra ID。