示例工程数据库教程后端【免费下载链接】sql-server-samplesAzure Data SQL Samples - Official Microsoft GitHub Repository containing code samples for SQL Server, Azure SQL, Azure Synapse, and Azure SQL Edge项目地址https://gitcode.com/gh_mirrors/sq/sql-server-samples点击查看免费下载本指南基于 sql-server-samples 仓库中的官方示例深入讲解如何借助 PolyBase 将存储在 Azure Blob Storage 中的 Wide World ImportersWWI公开数据集加载到 Azure SQL 数据仓库现称 Azure Synapse Analytics 专用 SQL 池的空库中。文章将完整拆解 load-sample-data-using-polybase.sql 的八个执行步骤涵盖外部数据源、外部文件格式、外部表、CTAS 加载、大数据量生成与统计信息维护等关键技术点并对照同仓库的 polybase 示例与 WWI 数据仓库项目源码帮助读者掌握一套可直接复制的外部数据 → 大规模分析表ELT 流水线。示例概览它解决的问题Wide World Importers 示例数据库包含 OLTP 数据库WideWorldImporters和 OLAP 数据库WideWorldImportersDW数据仓库。本示例的核心目标是把一个空的数据仓库快速填充为一份规模可观、可用于性能测试与分析演示的 WWI 分析数据集。其技术路径不是逐行 INSERT而是利用 PolyBase 直接读取公共 Blob 容器中的文本文件再通过 CTASCREATE TABLE AS SELECT并行装载为列存储索引表。示例要点适用范围Azure SQL 数据仓库Azure SQL Data Warehouse关键特性PolyBase、数据加载data loading编程语言T-SQL原始作者Casey Karst、Barbara Kress、Ayo Olubeko仓库 readme.md 记录脚本整体完成四件事创建以 Azure Blob 为数据源的外部表使用 CTAS 语句将数据装载进数据仓库创建存储过程为日期维表和销售事实表生成一年的数据可达百万行级为装载后的数据创建统计信息。运行前准备软件前提SQL Server Management StudioSSMS或 Visual Studio 2015 及以上版本并安装最新 SSDT。Azure 前提一个已存在且为空的 Azure SQL 数据仓库实例。数据仓库前提一个专门用于加载数据的登录名login与用户user。脚本注释明确指出服务器管理员账号server admin用于执行管理操作并不适合在用户数据上运行查询因此应单独创建加载专用账号并仅授予其执行本脚本所需的最小权限。运行步骤使用加载专用的登录名连接数据仓库整体执行 T-SQL 脚本建议在 SSMS 中以脚本方式逐步执行便于观察每步结果数据加载完成后即可在数据仓库上运行分析查询。脚本的头部注释还提醒执行前需保证 Azure 账号下已具备 SQL 数据仓库数据库若还没有需要先完成数据仓库的创建。脚本八步深度拆解脚本执行流程划分为八个步骤下面结合源码逐段解析。STEP 1创建外部数据源与外部文件格式CREATE EXTERNAL DATA SOURCE WWIStorage WITH ( TYPE Hadoop, LOCATION wasbs://wideworldimporterssqldwholdata.blob.core.windows.net );TYPE HadoopPolyBase 通过 Hadoop API 访问 Azure Blob Storage 中的数据即 wasb:// 协议。LOCATION存放 WWI 数据集的 Azure 存储账户及容器地址。随后定义外部文件的格式特征CREATE EXTERNAL FILE FORMAT TextFileFormat WITH ( FORMAT_TYPE DELIMITEDTEXT, FORMAT_OPTIONS ( FIELD_TERMINATOR |, USE_TYPE_DEFAULT FALSE ) );数据以文本形式存储字段之间使用管道符|分隔USE_TYPE_DEFAULT FALSE当某个字段缺失时不强制使用类型的默认值而是按原始缺失语义处理。对照阅读同仓库的 DemonstratePolybase.sql 展示了字段分隔符为逗号,的 CSV 变体其对应的外部文件格式为CommaDelimitedTextFileFormat。可以看到 PolyBase 对外部文本文件格式的建模是完全一致的只是字段分隔符不同。STEP 2创建逻辑 schemaCREATE SCHEMA ext; GO CREATE SCHEMA wwi; GOextexternal用于组织即将创建的外部表隔离数据源定义与真实数据wwiwarehouse wide world importers用于组织装载后的本地标准表。这种外部表与本地表分属不同 schema的做法为后续的加载与查询提供了清晰的对象边界。STEP 3创建外部表定义外部表的表结构定义存储在数据仓库中但表实际指向的数据位于 Azure Blob Storage。脚本共创建 12 张外部表其中 7 张维度表、5 张事实表外部表用途ext.dimension_City城市维度ext.dimension_Customer客户维度ext.dimension_Employee员工维度ext.dimension_PaymentMethod付款方式维度ext.dimension_StockItem库存商品维度ext.dimension_Supplier供应商维度ext.dimension_TransactionType交易类型维度ext.fact_Movement库存移动事实ext.fact_Order订单事实ext.fact_Purchase采购事实ext.fact_Sale销售事实作为种子数据ext.fact_StockHolding库存持有事实ext.fact_Transaction交易事实以城市维度为例其外部表定义如下CREATE EXTERNAL TABLE [ext].dimension_City NOT NULL, [State Province] nvarchar NOT NULL, [Country] nvarchar NOT NULL, [Continent] nvarchar NOT NULL, [Sales Territory] nvarchar NOT NULL, [Region] nvarchar NOT NULL, [Subregion] nvarchar NOT NULL, [Location] nvarchar NULL, [Latest Recorded Population] [bigint] NOT NULL, [Valid From] datetime2 NOT NULL, [Valid To] datetime2 NOT NULL, [Lineage Key] [int] NOT NULL ) WITH (LOCATION/v1/dimension_City/, DATA_SOURCE WWIStorage, FILE_FORMAT TextFileFormat, REJECT_TYPE VALUE, REJECT_VALUE 0 );关键参数LOCATIONBlob 容器内的目录路径如/v1/dimension_City/对应数据集按 v1 版本目录组织DATA_SOURCE引用 STEP 1 创建的WWIStorageFILE_FORMAT引用 STEP 1 创建的TextFileFormatREJECT_TYPE VALUE与REJECT_VALUE 0采用按行数拒绝的容错策略允许拒绝 0 行即严格模式结合USE_TYPE_DEFAULT FALSE任何不符合类型定义的记录都会导致加载失败而非被静默替换。其余 11 张外部表结构相同仅列定义与LOCATION目录不同。事实表如fact_Order、fact_Sale包含Sale Key、Total Excluding Tax、Tax Amount、Total Including Tax等典型销售事实度量列。源码佐证Configuration_ApplyPolybase存储过程Configuration_ApplyPolybase.sql展示了同样的三步式外部对象创建先建外部数据源AzureStoragewasbs://datasqldwdatasets.blob.core.windows.net再建逗号分隔的外部文件格式最后建外部表dbo.CityPopulationStatistics其中REJECT_VALUE 4用于跳过每个文件的 1 行表头。这印证了外部数据源 外部文件格式 外部表是 PolyBase 查询外部数据的三要素只是本脚本面向数据仓库做大规模装载而该存储过程面向 SQL Server 2016 做即席查询。STEP 4通过 CTAS 将外部数据装载为列存储表装载阶段使用CREATE TABLE AS SELECTCTAS语句直接以外部表为数据源建表。维度表采用REPLICATE分布事实表采用HASH分布全部使用CLUSTERED COLUMNSTORE INDEXCREATE TABLE [wwi].[dimension_City] WITH ( DISTRIBUTION REPLICATE, CLUSTERED COLUMNSTORE INDEX ) AS SELECT * FROM [ext].[dimension_City] OPTION (LABEL CTAS : Load [wwi].[dimension_City]) ;事实表示例哈希分布键选择事实表主键CREATE TABLE [wwi].[fact_Order] WITH ( DISTRIBUTION HASH([Order Key]), CLUSTERED COLUMNSTORE INDEX ) AS SELECT * FROM [ext].[fact_Order] OPTION (LABEL CTAS : Load [wwi].[fact_Order]) ;设计要点分布策略维度表REPLICATE每个计算节点各存一份全量副本避免 JOIN 时的数据移动事实表HASH(主键)按主键哈希均匀分布兼顾并行度与 JOIN 亲和性存储格式装载目标统一为聚集列存储索引这正是数据仓库高压缩比与高扫描性能的关键OPTION (LABEL ...)为每个 CTAS 请求打标签供后续步骤监控加载进度使用种子表销售事实先装载为wwi.seed_Sale而非直接命名fact_Sale因为它只作为后续大规模数据生成的种子数据源。STEP 5监控加载进度脚本注释说明此时正将数 GB 数据装载进数据仓库并压缩为列存储索引。仓库提供了一个可选的监控查询取消注释即可运行SELECT r.command, s.request_id, r.status, count(distinct input_name) as nbr_files, sum(s.bytes_processed)/1024/1024/1024 as gb_processed FROM sys.dm_pdw_exec_requests r INNER JOIN sys.dm_pdw_dms_external_work s ON r.request_id s.request_id WHERE r.[label] CTAS : Load [wwi].[dimension_City] OR ... GROUP BY r.command, s.request_id, r.status ORDER BY nbr_files desc, gb_processed desc;该查询通过sys.dm_pdw_exec_requests请求级与sys.dm_pdw_dms_external_work外部 DMS 工作级两个 DMV按 STEP 4 写入的label过滤出全部 CTAS 加载请求统计每个请求已处理的文件数与已处理 GB 数从而实时掌握整个装载进度。STEP 6生成百万级日期维表与销售事实表外部数据集并不包含dimension_Date和最终版fact_Sale脚本通过三个存储过程来生成1)wwi.InitialSalesDataPopulation—— 种子放大CREATE PROCEDURE [wwi].[InitialSalesDataPopulation] AS BEGIN INSERT INTO [wwi].[seed_Sale] ( ... ) SELECT ... FROM [wwi].[seed_Sale] INSERT INTO [wwi].[seed_Sale] ( ... ) SELECT ... FROM [wwi].[seed_Sale] INSERT INTO [wwi].[seed_Sale] ( ... ) SELECT ... FROM [wwi].[seed_Sale] END通过三次自插入self-insert将seed_Sale的行数放大 8 倍为后续按天生成销售数据提供充足的行样本。2)wwi.PopulateDateDimensionForYear—— 生成指定年份的日期维度CREATE PROCEDURE [wwi].[PopulateDateDimensionForYear] Year [int] AS BEGIN -- 临时表 #month月份 1-12 及每月天数含闰年判断 -- 临时表 #days1-31 INSERT [wwi].[dimension_Date] ( ... ) SELECT CAST(...) AS [Date] ,DAY(...) AS [Day Number] ,DATENAME(...) AS [Day] ... ,DATEPART(ISO_WEEK, ...) AS [ISO Week Number] FROM #month m CROSS JOIN #days d WHERE d.days m.numofdays END该过程以#month含闰年逻辑(YEAR % 4 0 AND YEAR % 100 0) OR YEAR % 400 0与#days两张 ROUND_ROBIN 分布的临时堆表做 CROSS JOIN按d.days m.numofdays过滤出该年每一天生成Date、Day Number、Day、Month、Short Month、Calendar/Fiscal各月/年编号与标签、ISO Week Number共 14 个日期维度属性。其中财年Fiscal Year规则为11、12 月归属下一财年11 月为财年第 1 个月。3)wwi.Configuration_PopulateLargeSaleTable—— 按天批量生成销售事实CREATE PROCEDURE [wwi].[Configuration_PopulateLargeSaleTable] EstimatedRowsPerDay [bigint], Year [int] AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; EXEC [wwi].[PopulateDateDimensionForYear] Year; ... WHILE DateCounter CAST(YEAR AS CHAR(4)) 1231 BEGIN SET variance (SELECT RAND() * 10)*.01 .95 SET VariantNumberOfSalesPerDay FLOOR(NumberOfSalesPerDay * variance) ... INSERT [wwi].[fact_Sale] ( ... ) SELECT TOP(VariantNumberOfSalesPerDay) ... FROM [wwi].[seed_Sale] WHERE [Invoice Date Key] CAST(YEAR AS CHAR(4)) -01-01 ORDER BY [Sale Key]; SET DateCounter DATEADD(day, 1, DateCounter); END; END;核心逻辑先调用PopulateDateDimensionForYear生成指定年份的日期维度确定起始销售键与最大日期通过RAISERROR实时输出当前处理到的日期每日销售量为EstimatedRowsPerDay乘以随机波动系数varianceRAND()*10*.01 .95即 95%105% 波动使数据更贴近真实分布从seed_Sale中按TOP(VariantNumberOfSalesPerDay)取样插入fact_Sale逐日推进直到年末。脚本末尾的实际调用EXEC [wwi].[InitialSalesDataPopulation] -- Populate wwi.fact_Sales with 100,000 rows per day for each day in the year 2000. EXEC [wwi].[Configuration_PopulateLargeSaleTable] 100000, 2000即先放大种子数据再以每天 10 万行、覆盖 2000 年全年的规模生成销售事实表一年约 3650 万行。数据生成期间可通过SELECT MAX([Invoice Date Key]) FROM wwi.fact_Sale;观察当前进度。STEP 7预热复制表缓存SELECT TOP 1 * FROM [wwi].[dimension_City]; SELECT TOP 1 * FROM [wwi].[dimension_Customer]; SELECT TOP 1 * FROM [wwi].[dimension_Date]; SELECT TOP 1 * FROM [wwi].[dimension_Employee]; SELECT TOP 1 * FROM [wwi].[dimension_PaymentMethod]; SELECT TOP 1 * FROM [wwi].[dimension_StockItem]; SELECT TOP 1 * FROM [wwi].[dimension_Supplier]; SELECT TOP 1 * FROM [wwi].[dimension_TransactionType];SQL 数据仓库的REPLICATE表通过在每个计算节点缓存数据副本来加速后续查询但缓存只在首次查询该表时才被填充。因此首次查询复制表可能需要额外时间之后查询会明显加快。此步骤通过 8 条SELECT TOP 1对全部复制维度表触发缓存填充。STEP 8为装载数据创建统计信息CREATE PROCEDURE [dbo].[prc_sqldw_create_stats] ( create_type tinyint -- 1 default 2 Fullscan 3 Sample , sample_pct tinyint ) AS ...该存储过程自动为所有本地表的所有列生成CREATE STATISTICS语句create_type 1默认方式默认采样create_type 2WITH FULLSCAN全扫描最准确但最耗时create_type 3WITH SAMPLE pct PERCENT按百分比采样sample_pct默认 20非法参数会通过THROW 151000, ...抛出错误。实现方式通过sys.tables/sys.schemas/sys.columns/sys.stats_columns元数据视图枚举列用LEFT JOIN sys.stats_columns过滤掉已有统计信息的列并排除外部表LEFT JOIN sys.external_tables且e.[object_id] IS NULL将生成的 DDL 写入#stats_ddl临时表DISTRIBUTION HASH([seq_nmbr])再循环sp_executesql逐条执行。脚本最后执行EXEC [dbo].[prc_sqldw_create_stats] 1, NULL;即对全部列创建统计信息确保查询优化器能够生成高质量的执行计划。仓库内关联实践PolyBase 即席查询示例除了大规模装载仓库还提供了 PolyBase 的查询侧示例polybase/README.md 与 DemonstratePolybase.sql适用于 SQL Server 2016 与 Azure SQL Database 上的WideWorldImportersDW数据库。该示例的业务场景是WWI 公司想找出近三年人口增长率超过 20% 且尚未开拓客户的城市用于扩张选址。其运行方式更简洁——只需一句EXEC [Application].Configuration_ApplyPolybase;该存储过程见 Configuration_ApplyPolybase.sql在运行时做了三项健康检查并动态创建 PolyBase 对象SERVERPROPERTY(NIsPolybaseInstalled) 0检查 PolyBase 是否安装未安装则仅输出警告sys.configurations中hadoop connectivity是否取值 1、4 或 7这些取值对应 Azure Storage 连接能力未启用则输出警告通过后动态执行CREATE EXTERNAL DATA SOURCE / FILE FORMAT / TABLE三个 DDL。随后即可像普通表一样查询外部数据并可与本地表 JOINWITH PotentialCities AS ( SELECT cps.CityName, cps.StateProvinceCode, MAX(cps.LatestRecordedPopulation) AS PopulationIn2016, (MAX(cps.LatestRecordedPopulation) - MIN(cps.LatestRecordedPopulation)) * 100.0 / MIN(cps.LatestRecordedPopulation) AS GrowthRate FROM dbo.CityPopulationStatistics AS cps WHERE cps.LatestRecordedPopulation IS NOT NULL AND cps.LatestRecordedPopulation 0 GROUP BY cps.CityName, cps.StateProvinceCode ), InterestingCities AS ( SELECT DISTINCT pc.CityName, pc.StateProvinceCode, pc.PopulationIn2016, FLOOR(pc.GrowthRate) AS GrowthRate FROM PotentialCities AS pc INNER JOIN Dimension.City AS c ON pc.CityName c.City WHERE GrowthRate 2.0 AND NOT EXISTS (SELECT 1 FROM Fact.Sale AS s WHERE s.[City Key] c.[City Key]) ) SELECT TOP(100) CityName, StateProvinceCode, PopulationIn2016, GrowthRate FROM InterestingCities ORDER BY PopulationIn2016 DESC;这个查询示范了 PolyBase 的完整价值外部 Blob 数据与本地表在同一个 T-SQL 语句中 JOIN无需任何数据搬迁。与本文主脚本相比一个面向装载load一个面向查询query二者共同构成 PolyBase 的两大典型用法。清理外部对象时依次执行DROP EXTERNAL TABLE / FILE FORMAT / DATA SOURCE即可。免责声明与适用边界仓库明确声明示例中包含的代码不应用于生产环境仅用于学习与演示本主脚本面向的是Azure SQL 数据仓库专用 SQL 池环境其wasbs://Hadoop 类型数据源、CTAS、复制表缓存等概念与该服务形态强相关同仓库的 DemonstratePolybase.sql 则适用于 SQL Server 2016需安装 PolyBase 并正确配置hadoop connectivity主脚本引用的是微软官方托管的公共 Blob 容器wasbs://wideworldimporterssqldwholdata.blob.core.windows.net该数据集为公开读取如需私有数据应使用带凭据的数据源访问方式。延伸阅读继续探索本仓库中的相关资源示例脚本目录包含 PolyBase 查询示例、内存 OLTP、行级安全、动态数据掩码等多个特性演示WideWorldImportersDW 的 SSDT 工程可查看Configuration_ApplyPolybase等应用存储过程的完整源码Wide World Importers 根 README了解 OLTP 库、DW 库、SSIS ETL 与工作负载驱动的整体结构。完成上述全部步骤后你的数据仓库中就拥有了带列存储索引、复制/哈希分布、完整统计信息以及百万级销售事实数据的 WWI 分析环境可以直接投入查询性能测试与数据分析演示。赞分享示例工程数据库教程后端【免费下载链接】sql-server-samplesAzure Data SQL Samples - Official Microsoft GitHub Repository containing code samples for SQL Server, Azure SQL, Azure Synapse, and Azure SQL Edge项目地址https://gitcode.com/gh_mirrors/sq/sql-server-samples点击查看免费下载相关推荐使用 PolyBase 将 Contoso 零售数据仓库从 Azure Blob 存储加载到 Azure SQL 数据仓库sql-server-samples 实战指南使用 PolyBase 将 Contoso 零售数据仓库从 Azure Blob 存储加载到 Azure SQL 数据仓库sql server samples示例工程数据库教程后端使用 PolyBase 将公共 Contoso 零售数据仓库加载到 Azure SQL Data Warehousesql-server-samples 完整实战指南使用 PolyBase 将公共 Contoso 零售数据仓库加载到 Azure SQL Data Warehousesql server samples 完整示例工程数据库教程后端Wide World Importers SSAS 多维项目实战指南基于 WideWorldImportersDW 构建 Analysis Services 多维数据集Wide World Importers SSAS 多维项目实战指南基于 WideWorldImportersDW 构建 Analysis Services示例工程数据库教程后端上一篇CANNBot TileLang代码格式检查下一篇UAMP数据库索引优化提升查询效率创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
