数据加载与处理：从COPY语句到Synapse无服务器SQL池

### 数据加载与处理：从 COPY 语句到 Synapse 无服务器 SQL 池 #### 1. 使用 COPY 语句将数据加载到专用 SQL 池 COPY 语句与 PolyBase 外部表不同，它不会与数据湖中的文件或文件夹建立永久连接，只是将数据从数据湖文件或文件夹加载到专用 SQL 池的表中。它不需要像 PolyBase 那样的额外对象，如外部数据源或外部文件格式，并且运行 COPY 语句所需的权限较低。 COPY 语句的优点还包括： - 可以在位置路径中指定不同的文件和通配符。 - 可以使用自定义行终止符。 - 可以为目标列设置默认值，并指定源到目标列的映射。 COPY 语句支持的文件类型有： | 文件类型 | | ---- | | CSV | | Parquet | | ORC | ##### 1.1 权限设置 使用 COPY 语句时，权限和安全设置与 PolyBase 不同。可以从以下源加载数据： - Azure Blob 存储 - Azure Data Lake Storage Gen2 根据存储类型和文件类型，需要不同的身份验证类型和驱动程序来连接存储并加载文件。读取 Data Lake Gen2 账户中的 Parquet 或 ORC 文件时，注意以下几点： - 共享访问签名身份验证需要使用 `.blob.core.windows.net` 端点。 - 托管服务标识身份验证需要使用 `.dfs.core.windows.net` 端点。 性能方面，`.blob` 端点更快。如果没有身份验证限制，可以考虑使用 `.blob` 端点。 COPY 语句的语法中有一个 `CREDENTIAL (Identity = '', SECRET = '')` 参数，其设置取决于所选的身份验证方法。 ##### 1.2 操作步骤 1. **创建表**： ```sql CREATE TABLE [YOURCOPYSTAGINGTABLE] ( [YEAR] VARCHAR(20) , [MONTH] VARCHAR(20) , DAY_OF_MONTH VARCHAR(20) , DAY_OF_WEEK VARCHAR(20) , FL_DATE VARCHAR(30) , UNIQUE_CARRIER VARCHAR(20) , FL_NUM VARCHAR(20) , ORIGIN_AIRPORT_ID VARCHAR(20) , ORIGIN VARCHAR(20) , ORIGIN_STATE_ABR VARCHAR(20) , DEST_AIRPORT_ID VARCHAR(20) , DEST VARCHAR(20) , DEST_STATE_ABR VARCHAR(20) , CRS_DEP_TIME VARCHAR(30) , DEP_TIME VARCHAR(30) , DEP_DELAY VARCHAR(20) , DEP_DELAY_NEW VARCHAR(20) , DEP_DEL15 VARCHAR(20) , DEP_DELAY_GROUP VARCHAR(20) , CRS_ARR_TIME VARCHAR(20) , ARR_TIME VARCHAR(20) , ARR_DELAY VARCHAR(20) , ARR_DELAY_NEW VARCHAR(20) , ARR_DEL15 VARCHAR(20) , ARR_DELAY_GROUP VARCHAR(20) , CANCELLED VARCHAR(20) , DIVERTED VARCHAR(20) , CRS_ELAPSED_TIME VARCHAR(20) , ACTUAL_ELAPSED_TIME VARCHAR(30) , FLIGHTS VARCHAR(20) , DISTANCE VARCHAR(20) , DISTANCE_GROUP VARCHAR(20) , Scored_Labels VARCHAR(50) ) WITH ( DISTRIBUTION = ROUND_ROBIN , HEAP ); ``` 2. **执行 COPY INTO 语句**： ```sql COPY INTO [YOURCOPYSTAGINGTABLE] FROM 'https://[YOURDATALAKEACCOUNT].dfs.core.windows.net/[YOURPATH]/airdelayspredict.csv' WITH ( FILE_TYPE = 'CSV' ,MAXERRORS = 0 ,FIELDTERMINATOR = ',' ,FIRSTROW = 2 ); ``` 这里没有使用 `CREDENTIALS` 参数，该语句使用凭据传递，将 Azure Active Directory 凭据推送到数据湖账户。 3. **查询加载的数据**： ```sql select * from [YOURCOPYSTAGINGTABLE]; ``` COPY 语句的另一个优点是可以在一个语句中从多个文件夹或文件读取数据，只需用逗号分隔不同的位置： ```sql FROM 'https://[YOURDATALAKEACCOUNT].dfs.core.windows.net/[YOURPATH]/airdelayspredict.csv', 'https://[YOURDATALAKEACCOUNT].dfs.core.windows.net/[YOURALTERNATIVEPATH]/airdelayspredict2.csv', 'https://[YOURDATALAKEACCOUNT].dfs.core.windows.net/[YOURADDITTIONALPATH]/*' ``` 与之前使用的 CTAS 语句不同，COPY INTO 语句将数据