05 Data Warehouse

2023-09-14 本文已影响0人山猪打不过家猪

1. 创建dedicated sql pool 08

2. 创建 synapse 服务 012

2.1 在synapse的Mange页面创建sql pool

image.png

dedicated sql pool是dwu模式，是IO和CPU混合模式，计算和储存为一体，他的性能和花费是可控的，所以用于每日或者主要任务，不使用的时候记得关闭，他的是按照小时计费的。
serverless pool是用于我们临时查询或者分析，类似于草稿纸

2.2 在synapse的Mange页面创建Apache spark pool 014

image.png

2.3 使用sql pool 015

建表和查询（过）
我的服务器资源将专门留给sql pool使用，这就意味着，如果sql pool在运行期间，serverless pool有可能回因为资源不足无法运行。

2.4 使用Aparche Spark NoteBook 016

添加NYC taxi开放数据集进来

image.png
需求：创建一个新的spark pool02，将taxi数据从sql pool01里移动到02之中，将数据在spark里处理后，返回到sql pool01里面

在spark里创建一个新的表，将sql pool里的trip表迁移过来

%%spark
spark.sql("CREATE DATABASE IF NOT EXISTS nyctaxi")
val df = spark.read.sqlanalytics("SQLPOOL1.dbo.Trip") 
df.write.mode("overwrite").saveAsTable("nyctaxi.trip")

使用pyspark,清洗数据，将无行程，但是有距离的乘客修改,并存为新表

%%pyspark
df = spark.sql("""
   SELECT PassengerCount,
       SUM(TripDistanceMiles) as SumTripDistance,
       AVG(TripDistanceMiles) as AvgTripDistance
   FROM nyctaxi.trip
   WHERE TripDistanceMiles > 0 AND PassengerCount > 0
   GROUP BY PassengerCount
   ORDER BY PassengerCount
""") 
display(df)
df.write.saveAsTable("nyctaxi.passengercountstats")

将上面的表，重新写回sql pool里

%%spark
val df = spark.sql("SELECT * FROM nyctaxi.passengercountstats")
df.write.sqlanalytics("SQLPOOL1.dbo.PassengerCountStats", Constants.INTERNAL )

2.5 使用Serverless Sql pool 017

我们分析不同资源的数据可以使用Serverless，类似于草稿
例如：马上想去分析以下spark pool里的taxi里的passengercounts表
在serverless pool里，我们可以看到没有table这个栏目，只有External tables用来关联外部表和文件

创建表格，添加外部链接

CREATE DATABASE mydbname

-- Create data source
-- create master key that will protect the credentials:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'sknfd#isfnasdkf!bsk1'

-- create credentials for containers in our demo storage account
CREATE DATABASE SCOPED CREDENTIAL sqlondemand
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'
GO
CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);

2.5 使用Synapse里的Copy Data tool 018

将数据从sqlserver 里复制到sql pool01里
注意：我们在迁移数据的时候，如果不需要整表迁移，我们可以使用隔壁的use query来使用sql筛选，然后再进行迁移

image.png

3.Synapse MMP Architechture

控制节点，计算节点，储存节点，总共60个节点

image.png

3.1 Sharding Patterns 027

Hash：大型表性能高，使用Hash分布各个表的资源，创建时需要给出指定的KEY，KEY最好是固定的，不能更新的，且大于60个，这样可以避免数据倾斜

image.png

2.Round-Robin：将表的资源均匀分配到各个节点中，平均分类。

image.png

3.Replicated:为小表提供最快的查询性能，每个节点上都有表的完整副本，由于是小表，节省了节点之间的传输和斜调的时间，所以很快

image.png

3.2 创建合适的表 028

image.png

3.3 Table types 029

Clustered columnstore用于大型表，压缩，默认

image.png
heap用于临时表或者小型的永久性表，无压缩

image.png
Clustered Index 最常见的表类型，Fact表常用

image.png

3.4 Partitioning 分区030

不能创建太多分区，分区的数据尽可能100w,

3.5 练习 032

假设我们已有一张3700w条数据的表，在我们的本地数据库

首先，我们查看，如果使用robin平均分配数据，数据情况

select cp.distribution,count(*) distributionRecords from (
select row_number() over(order by (select null)) rowNum from FactTransactionHistory) subq 
cross apply(select rowNum%60 distribution) cp 
group by distribution
order by distribution