使用Spark載入資料到SQL Server列儲存表

覆周發表於2021-03-03
SparkSQLServer

原文地址https://devblogs.microsoft.com/azure-sql/partitioning-on-spark-fast-loading-clustered-columnstore-index/

介紹

SQL Server的Bulk load預設為序列，這意味著例如，一個BULK INSERT語句將生成一個執行緒將資料插入表中。但是，對於併發負載，您可以使用多個批量插入語句插入同一張表，前提是需要閱讀多個檔案。

考慮要求所在的情景：

  • 從大檔案載入資料（比如，超過 20 GB）
  • 拆分檔案不是一個選項，因為它將是整個大容量負載操作中的一個額外步驟。
  • 每個傳入的資料檔案大小不同，因此很難識別大塊數（將檔案拆分為）並動態定義為每個大塊執行的批量插入語句。
  • 要載入的多個檔案跨越多個 GB（例如超過 20 GB 及以上），每個GB 包含數百萬條記錄。

在這種情況下，使用 Apache Spark是並行批量資料載入到 SQL 表的流行方法之一。

在本文中，我們使用 Azure Databricks spark engine使用單個輸入檔案將資料以並行流（多個執行緒將資料載入到表中）插入 SQL Server。目標表可能是Heap、Clustered Index或Clustered Columnstore Index。本文旨在展示如何利用Spark提供的高度分散式框架，在載入到 SQL Server或 Azure SQL的聚集列儲存索引表之前仔細對資料分割槽。

本文中分享的最有趣的觀察是展示使用Spark預設配置時列儲存表的行組質量降低，以及如何通過高效使用Spark分割槽來提高質量。從本質上講，提高行組質量是決定查詢效能的重要因素。

 

環境設定

資料集：

  • 單張表的一個自定義資料集。一個 27 GB 的 CSV 檔案，110 M 記錄，共 36 列。其中列的型別有int, nvarchar, datetime等。

資料庫：

  • Azure SQL Database – Business Critical, Gen5 80vCores

ELT 平臺：

  • Azure Databricks – 6.6 (includes Apache Spark 2.4.5, Scala 2.11)
  • Standard_DS3_v2 14.0 GB Memory, 4 Cores, 0.75 DBU (8 Worker Nodes Max)

儲存：

  • Azure Data Lake Storage Gen2

先決條件：

在進一步瀏覽本文之前，請花一些時間瞭解此處將資料載入到聚集列儲存表中的概述：Data Loading performance considerations with Clustered Columnstore indexes

在此測試中，資料從位於 Azure Data Lake Storage Gen 2的 CSV 檔案中載入。CSV 檔案大小為 27 GB，有 110 M 記錄，有 36 列。這是一個帶有隨機資料的自定義資料集。

批量載入或預處理（ELT\ETL）的典型架構看起來與下圖相似：

使用BULK INSERTS    

在第一次測試中，單個BULK INSERT用於將資料載入到帶有聚集列儲存索引的 Azure SQL 表中，這裡沒有意外，根據所使用的 BATCHSIZE，它花了 30 多分鐘才完成。請記住，BULK INSERT是一個單一的執行緒操作，因此單個流會讀取並將其寫入表中，從而降低負載吞吐量。

 

使用Azure Databricks

為了實現寫入到 SQL Server和讀取ADLS (Azure Data Lake Storage) Gen 2的最大併發性和高吞吐量，Azure Databricks 被選為平臺的選擇，儘管我們還有其他選擇，即 Azure Data Factory或其他基於Spark引擎的平臺。

使用Azure Databricks載入資料的優點是 Spark 引擎通過專用的 Spark API並行讀取輸入檔案。這些 API將使用一定數量的分割槽，這些分割槽對映到單個或多個輸入檔案，對映是在檔案的一部分或整個檔案上完成的。資料讀入Spark DataFrame or, DataSet or RDD (Resilient Distributed Dataset) 。在這種情況下，資料被載入到DataFrame中，然後進行轉換（設定與目標表匹配的DataFrame schema），然後資料準備寫入 SQL 表。

要將DataFrame中的資料寫入 SQL Server中，必須使用Microsoft's Apache Spark SQL Connector。這是一個高效能的聯結器，使您能夠在大資料分析中使用事務資料，和持久化結果用於即席查詢或報告。聯結器允許您使用任何 SQL Server（本地資料庫或雲中）作為 Spark 作業的輸入資料來源或輸出目標。

　　GitHub repo: Fast Data Loading in Azure SQL DB using Azure Databricks

請注意，目標表具有聚集列儲存索引，以實現高負載吞吐量，但是，您也可以將資料載入到Heap，這也將提供良好的負載效能。對於本文的相關性，我們只討論載入到列儲存表。我們使用不同的 BATCHSIZE 值將資料載入到Clustered Columnstore Index中 -請參閱此文件，瞭解 BATCHSIZE 在批量載入到聚集列儲存索引表期間的影響。

以下是Clustered Columnstore Index上的資料載入測試執行，BATCHSIZE為 102400 和 1048576：

 

 

 

 

請注意，我們正在使用 Azure Databricks使用的預設並行和分割槽，並將資料直接推至 SQL Server聚集列儲存索引表。我們沒有調整 Azure Databricks使用的任何預設配置。無論所定義的批次大小，我們所有的測試都大致在同一時間完成。

將資料載入到 SQL 中的 32 個併發執行緒是由於上述已提供的資料磚群集的大小。該叢集最多有 8 個節點，每個節點有 4 個核心，即 8*4 = 32 個核心，最多可執行 32 個併發執行緒。

檢視行組(Row Groups)

有關我們使用 BATCHSIZE 1048576 插入資料的表格，以下是在 SQL 中建立的行組數：

行組總數：

SELECT COUNT(1)
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('largetable110M_1048576')
216 

行組的質量：

SELECT *
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('largetable110M_1048576')

在這種情況下，我們只有一個delta store在OPEN狀態 （total_rows = 3810） 和 215 行組處於壓縮狀態， 這是有道理的， 因為如果插入的批次大小是>102400 行， 資料不再delta store儲存， 而是直接插入一個壓縮行組的列儲存。在這種情況下，壓縮狀態中的所有行組都有 >102400 條記錄。現在，有關行組的問題是：

為什麼我們有216行組？

為什麼當我們的BatchSize設定為 1048576 時，每個行組的行數不同？

請注意，每個行組的資料大約等於上述結果集中的 500，000 條記錄。

這兩個問題的答案是 Azure Databricks Spark引擎對資料分割槽控制了寫入聚集列儲存索引錶行組的資料行數。讓我們來看看 Azure Databricks為有關資料集建立的分割槽數： 

# Get the number of partitions before re-partitioning
print(df_gl.rdd.getNumPartitions())
216

因此，我們為資料集建立了 216 個分割槽。請記住，這些是分割槽的預設數。每個分割槽都有大約 500000 條記錄。 

# Number of records in each partition
from pyspark.sql.functions
import spark_partition_id
df_gl.withColumn("partitionId", spark_partition_id()).groupBy("partitionId").count().show(10000)

將Spark分割槽中的記錄數與行組中的記錄數進行比較，您就會發現它們是相等的。甚至分割槽數也等於行組數。因此，從某種意義上說，1048576 的 BATCHSIZE 正被每個分割槽中的行數過度拉大。 

sqldbconnection = dbutils.secrets.get(scope = "sqldb-secrets", key = "sqldbconn")
sqldbuser = dbutils.secrets.get(scope = "sqldb-secrets", key = "sqldbuser")
sqldbpwd = dbutils.secrets.get(scope = "sqldb-secrets", key = "sqldbpwd")

servername = "jdbc:sqlserver://" + sqldbconnection url = servername + ";" + "database_name=" + <Your Database Name> + ";"
table_name = "<Your Table Name>"

# Write data to SQL table with BatchSize 1048576
df_gl.write \
.format("com.microsoft.sqlserver.jdbc.spark") \
.mode("overwrite") \
.option("url", url) \
.option("dbtable", table_name) \
.option("user", sqldbuser) \
.option("password", sqldbpwd) \
.option("schemaCheckEnabled", False) \
.option("BatchSize", 1048576) \
.option("truncate", True) \
.save()

行組質量

行組質量由行組數和每個行組記錄決定。由於聚集列儲存索引通過掃描單行組的列段掃描表，則最大化每個行組中的行數可增強查詢效能。當行組具有大量行數時，資料壓縮會改善，這意味著從磁碟中讀取的資料更少。為了獲得最佳的查詢效能，目標是最大限度地提高聚集列索引中每個行組的行數。行組最多可有 1048576 行。但是，需要注意的是，由於聚集列索引，行組必須至少有 102400 行才能實現效能提升。此外，請記住，行組的最大大小（100萬）可能在每一個情況下都達到，檔案行組大小不只是最大限制的一個因素，但受到以下因素的影響。

  • 字典大小限制，即 16 MB
  • 插入指定的批次大小
  • 表的分割槽方案，因為行組不跨分割槽
  • 記憶體壓力導致行組被修剪
  • 索引重組，重建

話雖如此，現在一個重要的考慮是讓行組大小盡可能接近 100 萬條記錄。在此測試中，由於每個行組的大小接近 500000 條記錄，我們有兩個選項可以達到約 100 萬條記錄的大小：

  • 在Spark中，更改分割槽數，使每個分割槽儘可能接近 1048576 條記錄，
  • 保持Spark分割槽（預設值），一旦資料載入到表中，就執行 ALTER INDEX REORG，將多個壓縮行組組合成一組。

選項#1很容易在Python或Scala程式碼中實現，該程式碼將在Azure Databricks上執行，負載相當低。

選項#2是資料載入後需要採取的額外步驟，當然，這將消耗 SQL 上的額外 CPU ，並增加整個載入過程所需的時間。

為了保持本文的相關性，讓我們來討論更多關於Spark分割槽，以及如何從其預設值及其在下一節的影響中更改它。

 

Spark Partitioning

Spark 引擎最典型的輸入源是一組檔案，這些檔案通過將每個節點上的適當分割槽劃分為一個或多個 Spark API來讀取這些檔案。這是 Spark 的自動分割槽，將使用者從確定分割槽數量的憂慮中抽象出來，如果使用者想挑戰，就需控制分割槽的配置。根據環境和環境設定計算的分割槽的預設數通常適用於大多數情況下。但是，在某些情況下，更好地瞭解分割槽是如何自動計算的，如果需要，使用者可以更改分割槽計數，從而在效能上產生明顯差異。

注意：大型Spark群集可以生成大量並行執行緒，這可能導致 Azure SQL DB 上的記憶體授予爭議。由於記憶體超時，您必須留意這種可能性，以避擴音前修剪。請參閱本文以瞭解更多詳細資訊，瞭解表的模式和行數等也可能對記憶體授予產生影響。

spark.sql.files.maxPartitionBytes是控制分割槽大小的重要引數，預設設定為128 MB。它可以調整以控制分割槽大小，因此也會更改由此產生的分割槽數。

spark.default.parallelism這相當於worker nodes核心的總數。

最後，我們有coalesce()和repartition()，可用於增加/減少分割槽數，甚至在資料已被讀入Spark。

只有當您想要減少分割槽數時，才能使用coalesce() ，因為它不涉及資料的重排。請考慮此data frame的分割槽數為 16，並且您希望將其增加到 32，因此您決定執行以下命令。

df = df.coalesce(32)
print(df.rdd.getNumPartitions())

但是，分割槽數量不會增加到 32 個，並且將保持在 16 個，因為coalesce()不涉及資料重排。這是一個效能優化的實現，因為無需昂貴的資料重排即可減少分割槽。

如果您想將上述示例的分割槽數減少到 8，則會獲得預期的結果。

df = df.coalesce(8)
print(df.rdd.getNumPartitions())

這將合併資料併產生 8 個分割槽。

repartition() 是另一個幫助調整分割槽的函式。對於同一示例，您可以使用以下命令將資料放入 32 個分割槽。

df = df.repartition(32)
print(df.rdd.getNumPartitions())

最後，還有其他功能可以改變分割槽數，其中是groupBy(), groupByKey(), reduceByKey() 和 join()。當在 DataFrame 上呼叫這些功能時，會導致跨機器或通常跨執行器對資料進行重排，最終在預設情況下將資料重新劃分為 200 個分割槽。此預設 數字可以使用spark.sql.shuffle.partitions配置進行控制。

 

資料載入

現在，瞭解分割槽在 Spark 中的工作原理以及如何更改分割槽，是時候實施這些學習了。在上述實驗中，分割槽數為 216（預設情況下），這是因為檔案的大小為 27 GB，因此將 27 GB 除以 128 MB（預設情況下由 Spark 定義的最大分割槽位元組）提供了216 個分割槽。

Spark重新分割槽的影響

對 PySpark 程式碼的更改是重新分割槽資料並確保每個分割槽現在有 1048576 行或接近它。為此，首先在DataFrame中獲取記錄數量，然後將其除以 1048576。此劃分的結果將是用於載入資料的分割槽數，假設分割槽數為n。但是，可能有一些分割槽現在有 >=1048576 行，因此，為了確保每個分割槽都<=1048576行，我們將分割槽數作為n+1。使用n+1在分割槽結果為 0 的情況下也很重要。在這種情況下，您將有一個分割槽。

由於資料已載入到DataFrame中，而 Spark 預設已建立分割槽，我們現在必須再次重新分割槽資料，分割槽數等於n+1。

# Get the number of partitions before re-partitioning
print(df_gl.rdd.getNumPartitions())
216
 
# Get the number of rows of DataFrame and get the number of partitions to be used.
rows = df_gl.count()
n_partitions = rows//1048576

# Re-Partition the DataFrame
df_gl_repartitioned = df_gl.repartition(n_partitions+1)
 
# Get the number of partitions after re-partitioning
print(df_gl_repartitioned.rdd.getNumPartitions())
105
 
# Get the partition id and count of partitions
df_gl_repartitioned.withColumn("partitionId",spark_partition_id()).groupBy("partitionId").count().show(10000)

因此，在重新劃分分割槽後，分割槽數量從216 個減少到 105 （n+1），因此每個分割槽現在都有接近1048576行。

此時，讓我們將資料再次寫入 SQL 表中，並驗證行組質量。這一次，每個行組的行數將接近每個分割槽中的行數（略低於 1048576）。讓我們看看下面：

重新分割槽後的行組

SELECT COUNT(1)
FROM sys.dm_db_column_store_row_group_physical_stats 
WHERE object_id = OBJECT_ID('largetable110M_1048576')
105

重新分割槽後的行組質量

從本質上講，這次整體資料載入比之前慢了 2 秒，但行組的質量要好得多。行組數量減少到一半，行組幾乎已填滿到最大容量。請注意，由於DataFrame的重新劃分，將消耗額外的時間，這取決於資料幀的大小和分割槽數。

請注意，您不會總是獲得每row_group 100 萬條記錄。它將取決於資料型別、列數等，以及之前討論的因素-請參閱sys.dm_db_column_store_row_group_physical_stats

 

關鍵點

  1. 建議在將資料批量載入到 SQL Server時使用BatchSize（無論是 CCI 還是Heap）。但是，如果 Azure Databricks 或任何其他 Spark 引擎用於載入資料，則資料分割槽在確定聚集列儲存索引中的行組質量方面起著重要作用。
  2. 使用BULK INSERT命令載入資料將遵守命令中提到的BATCHSIZE，除非其他因素影響插入行組的行數。
  3. Spark 中的資料分割槽不應基於某些隨機數，最好動態識別分割槽數，並將n+1 用作分割槽數。
  4. 由於聚集列儲存索引通過掃描單行組的列段掃描表，則最大化每個行組中的記錄數可增強查詢效能。為了獲得最佳的查詢效能，目標是最大限度地提高聚集列儲存索引中每個行組的行數。
  5. Azure Databricks的資料載入速度在很大程度上取決於選擇的叢集型別及其配置。此外，請注意，到目前為止，Azure Databricks聯結器僅支援Apache Spark 2.4.5。微軟已經發布了對Spark 3.0的支援，它目前在預覽版中，我們建議您在開發測試環境中徹底測試此聯結器。
  6. 根據data frame的大小、列數、資料型別等，進行重新劃分的時間會有所不同，因此您必須從端端角度考慮這次對整體資料載入的考慮。

 

Azure Data Factory

這是一篇非常好的資料ETL文章，Spark和SQL Server列儲存表功能的組合。

Azure Data Factory是當前最成熟，功能最強大的ETL/ELT資料整合服務。其架構就是使用Spark作為計算引擎。

https://github.com/mrpaulandrew/A-Day-Full-of-Azure-Data-Factory

相關文章