表批讀和寫

創建一個表

三角洲湖支持創建兩種類型的tables-tables metastore和表中定義所定義的路徑。

你可以通過以下方式創建表。

• SQL DDL命令:您可以使用標準的SQL DDL命令支持Apache火花(例如,創建表和取代表三角洲)來創建表。

  創建表如果不存在默認的。people10m(idINT,firstName字符串,middleName字符串,姓字符串,性別字符串,生日時間戳,ssn字符串,工資INT)使用δ創建或取代表默認的。people10m(idINT,firstName字符串,middleName字符串,姓字符串,性別字符串,生日時間戳,ssn字符串,工資INT)使用δ

  請注意

  在磚運行時8.0及以上,三角洲湖是默認的格式和你不需要使用δ。

  請注意

  在磚運行時7.0及以上,以避免最終一致性問題AWS S3,磚推薦使用創建或取代語法,而不是下降表緊隨其後的是一個創建表。

  在磚運行時7.0及以上,SQL還支持創建表在一個蜂巢metastore路徑不創建一個條目。

  ——創建或替換表的路徑創建或取代表δ。' /tmp/δ/people10m”(idINT,firstName字符串,middleName字符串,姓字符串,性別字符串,生日時間戳,ssn字符串,工資INT)使用δ

• DataFrameWriterAPI:如果你想同時創建一個表,將數據插入火花DataFrames或數據集,您可以使用火花DataFrameWriter(Scala或Java和Python)。

  #在使用DataFrame metastore的模式中創建表並寫入數據df。寫。格式(“δ”)。saveAsTable(“default.people10m”)#使用DataFrame創建或替換分區表路徑模式和寫/覆蓋數據df。寫。格式(“δ”)。模式(“覆蓋”)。保存(“/ tmp /δ/ people10m”)

  / /創建表使用DataFrame metastore的模式和寫入數據df。寫。格式(“δ”)。saveAsTable(“default.people10m”)/ /創建表路徑使用DataFrame的模式和寫入數據df。寫。格式(“δ”)。模式(“覆蓋”)。保存(“/ tmp /δ/ people10m”)

  • 在磚運行時8.0及以上,三角洲湖是默認格式,您不需要指定使用δ,格式(“δ”),或使用(“δ”)。

  • 在磚運行時的7.0及以上,您還可以創建使用火花三角洲表DataFrameWriterV2API。

• DeltaTableBuilderAPI:您還可以使用DeltaTableBuilderAPI在三角洲湖創建表。相比DataFrameWriter API,該API可以更容易地指定附加信息,如專欄評論,表屬性,和生成的列。

  預覽

  這個特性是在公共預覽。

  請注意

  這個特性可以在磚運行時8.3及以上。

  #在metastore創建表DeltaTable。createIfNotExists(火花)\。的表(“default.people10m”)\。addColumn(“id”,“INT”)\。addColumn(“firstName”,“字符串”)\。addColumn(“middleName”,“字符串”)\。addColumn(“姓”,“字符串”,評論=“姓”)\。addColumn(“性別”,“字符串”)\。addColumn(“生日”,“時間戳”)\。addColumn(“ssn”,“字符串”)\。addColumn(“工資”,“INT”)\。執行()#創建或替換表與路徑,並添加屬性DeltaTable。createOrReplace(火花)\。addColumn(“id”,“INT”)\。addColumn(“firstName”,“字符串”)\。addColumn(“middleName”,“字符串”)\。addColumn(“姓”,“字符串”,評論=“姓”)\。addColumn(“性別”,“字符串”)\。addColumn(“生日”,“時間戳”)\。addColumn(“ssn”,“字符串”)\。addColumn(“工資”,“INT”)\。財產(“描述”,“與人數據表”)\。位置(“/ tmp /δ/ people10m”)\。執行()

  / /創建metastore表DeltaTable。createOrReplace(火花)。的表(“default.people10m”)。addColumn(“id”,“INT”)。addColumn(“firstName”,“字符串”)。addColumn(“middleName”,“字符串”)。addColumn(DeltaTable。columnBuilder(“姓”)。數據類型(“字符串”)。評論(“姓”)。構建())。addColumn(“姓”,“字符串”,評論=“姓”)。addColumn(“性別”,“字符串”)。addColumn(“生日”,“時間戳”)。addColumn(“ssn”,“字符串”)。addColumn(“工資”,“INT”)。執行()/ /創建或替換表與路徑,並添加屬性DeltaTable。createOrReplace(火花)。addColumn(“id”,“INT”)。addColumn(“firstName”,“字符串”)。addColumn(“middleName”,“字符串”)。addColumn(DeltaTable。columnBuilder(“姓”)。數據類型(“字符串”)。評論(“姓”)。構建())。addColumn(“姓”,“字符串”,評論=“姓”)。addColumn(“性別”,“字符串”)。addColumn(“生日”,“時間戳”)。addColumn(“ssn”,“字符串”)。addColumn(“工資”,“INT”)。財產(“描述”,“與人數據表”)。位置(“/ tmp /δ/ people10m”)。執行()

看到API文檔獲取詳細信息。

另請參閱創建一個表。

——在metastore創建表創建表默認的。people10m(idINT,firstName字符串,middleName字符串,姓字符串,性別字符串,生日時間戳,ssn字符串,工資INT)使用δ分區通過(性別)

df。寫。格式(“δ”)。partitionBy(“性別”)。saveAsTable(“default.people10m”)DeltaTable。創建(火花)\。的表(“default.people10m”)\。addColumn(“id”,“INT”)\。addColumn(“firstName”,“字符串”)\。addColumn(“middleName”,“字符串”)\。addColumn(“姓”,“字符串”,評論=“姓”)\。addColumn(“性別”,“字符串”)\。addColumn(“生日”,“時間戳”)\。addColumn(“ssn”,“字符串”)\。addColumn(“工資”,“INT”)\。partitionedBy(“性別”)\。執行()

df。寫。格式(“δ”)。partitionBy(“性別”)。saveAsTable(“default.people10m”)DeltaTable。createOrReplace(火花)。的表(“default.people10m”)。addColumn(“id”,“INT”)。addColumn(“firstName”,“字符串”)。addColumn(“middleName”,“字符串”)。addColumn(DeltaTable。columnBuilder(“姓”)。數據類型(“字符串”)。評論(“姓”)。構建())。addColumn(“姓”,“字符串”,評論=“姓”)。addColumn(“性別”,“字符串”)。addColumn(“生日”,“時間戳”)。addColumn(“ssn”,“字符串”)。addColumn(“工資”,“INT”)。partitionedBy(“性別”)。執行()

選擇數(*)>0作為”分區存在”從默認的。people10m在哪裏性別=“M”

顯示(火花。sql(“SELECT COUNT(*) > 0的分區存在違約。people10m性別= M”))

顯示(火花。sql(“SELECT COUNT(*) > 0的分區存在違約。people10m性別= M”))

創建表默認的。people10m(idINT,firstName字符串,middleName字符串,姓字符串,性別字符串,生日時間戳,dateOfBirth日期生成的總是作為(投(生日作為日期)),ssn字符串,工資INT)使用δ分區通過(性別)

DeltaTable。創建(火花)\。的表(“default.people10m”)\。addColumn(“id”,“INT”)\。addColumn(“firstName”,“字符串”)\。addColumn(“middleName”,“字符串”)\。addColumn(“姓”,“字符串”,評論=“姓”)\。addColumn(“性別”,“字符串”)\。addColumn(“生日”,“時間戳”)\。addColumn(“dateOfBirth”,DateType(),generatedAlwaysAs=“鑄(生日日期)”)\。addColumn(“ssn”,“字符串”)\。addColumn(“工資”,“INT”)\。partitionedBy(“性別”)\。執行()

DeltaTable。創建(火花)。的表(“default.people10m”)。addColumn(“id”,“INT”)。addColumn(“firstName”,“字符串”)。addColumn(“middleName”,“字符串”)。addColumn(DeltaTable。columnBuilder(“姓”)。數據類型(“字符串”)。評論(“姓”)。構建())。addColumn(“姓”,“字符串”,評論=“姓”)。addColumn(“性別”,“字符串”)。addColumn(“生日”,“時間戳”)。addColumn(DeltaTable。columnBuilder(“dateOfBirth”)。數據類型(DateType)。generatedAlwaysAs(“鑄(dateOfBirth日期)”)。構建())。addColumn(“ssn”,“字符串”)。addColumn(“工資”,“INT”)。partitionedBy(“性別”)。執行()

創建表事件(eventId長整型數字,數據字符串,eventType字符串,eventTime時間戳,eventDate日期生成的總是作為(投(eventTime作為日期)))使用δ分區通過(eventType,eventDate)

選擇*從事件在哪裏eventTime> =“2020-10-01”就是< =“2020-10-01 12:00:00”

創建表事件(eventId長整型數字,數據字符串,eventType字符串,eventTime時間戳,一年INT生成的總是作為(一年(eventTime)),月INT生成的總是作為(月(eventTime)),一天INT生成的總是作為(一天(eventTime)))使用δ分區通過(eventType,一年,月,一天)

選擇*從事件在哪裏eventTime> =“2020-10-01”就是< =“2020-10-01 12:00:00”

集火花。磚。δ。屬性。違約。appendOnly=真正的

讀一個表

你可以加載一個三角洲表DataFrame通過指定表名或路徑:

選擇*從默認的。people10m——metastore查詢表選擇*從δ。' /tmp/δ/people10m”——查詢表的道路

火花。表(“default.people10m”)# metastore查詢表火花。讀。格式(“δ”)。負載(“/ tmp /δ/ people10m”)#查詢表的路徑

火花。表(“default.people10m”)/ / metastore查詢表火花。讀。格式(“δ”)。負載(“/ tmp /δ/ people10m”)/ /創建表的路徑進口io。δ。值得一提的。_火花。讀。δ(“/ tmp /δ/ people10m”)

返回的DataFrame自動讀表的最新快照查詢;你永遠不需要運行刷新表。三角洲湖自動使用分區和數據讀取的最小數量數據時適用的謂詞的查詢。

查詢一個表(舊的快照時間旅行)

三角洲湖時間旅行允許您查詢一個年長的三角洲表的快照。時間旅行有許多用例,包括:

• 重新創建分析、報告、或輸出(例如,機器學習模型)的輸出。這對調試或審計可能是有用的,尤其是在監管的行業。

• 編寫複雜的時態查詢。

• 在您的數據修複錯誤。

• 為一組提供快照隔離快速變化的表的查詢。

本節描述表的查詢舊版本的支持方法,數據存儲問題,並提供了示例。

選擇*從table_name時間戳作為的timestamp_expression選擇*從table_name版本作為的版本

選擇*從默認的。people10m時間戳作為的2018 - 10 - 18 t22:15:12.013z選擇*從δ。' /tmp/δ/people10m”版本作為的123年

df1=火花。讀。格式(“δ”)。選項(“timestampAsOf”,timestamp_string)。負載(“/ tmp /δ/ people10m”)df2=火花。讀。格式(“δ”)。選項(“versionAsOf”,版本)。負載(“/ tmp /δ/ people10m”)

latest_version=火花。sql(“選擇馬克斯(版本)(描述曆史delta. / tmp /δ/ people10m)”)。收集()df=火花。讀。格式(“δ”)。選項(“versionAsOf”,latest_version(0][0])。負載(“/ tmp /δ/ people10m”)

選擇*從默認的。people10m@20190101000000000選擇*從默認的。people10m@v123

火花。讀。格式(“δ”)。負載(“/ tmp /δ/ people10m@20190101000000000”)# 2019-01-01 00:00:00.000表火花。讀。格式(“δ”)。負載(“/ tmp /δ/ people10m@v123”)123 #表版本

插入成my_table選擇*從my_table時間戳作為的date_sub(當前日期(),1)在哪裏用戶標識=111年

合並成my_table目標使用my_table時間戳作為的date_sub(當前日期(),1)源在源。用戶標識=目標。用戶標識當匹配然後更新集*

選擇數(截然不同的用戶標識)- - - - - -(選擇數(截然不同的用戶標識)從my_table時間戳作為的date_sub(當前日期(),7))

寫一個表

插入成默認的。people10m選擇*從更多人

df。寫。格式(“δ”)。模式(“添加”)。保存(“/ tmp /δ/ people10m”)df。寫。格式(“δ”)。模式(“添加”)。saveAsTable(“default.people10m”)

df。寫。格式(“δ”)。模式(“添加”)。保存(“/ tmp /δ/ people10m”)df。寫。格式(“δ”)。模式(“添加”)。saveAsTable(“default.people10m”)進口io。δ。值得一提的。_df。寫。模式(“添加”)。δ(“/ tmp /δ/ people10m”)

插入覆蓋表默認的。people10m選擇*從更多人

df。寫。格式(“δ”)。模式(“覆蓋”)。保存(“/ tmp /δ/ people10m”)df。寫。格式(“δ”)。模式(“覆蓋”)。saveAsTable(“default.people10m”)

df。寫。格式(“δ”)。模式(“覆蓋”)。保存(“/ tmp /δ/ people10m”)df。寫。格式(“δ”)。模式(“覆蓋”)。saveAsTable(“default.people10m”)進口io。δ。值得一提的。_df。寫。模式(“覆蓋”)。δ(“/ tmp /δ/ people10m”)

df。寫\。格式(“δ”)\。模式(“覆蓋”)\。選項(“replaceWhere”,“start_date > = ' 2017-01-01 '和end_date < = 2017-01-31”)\。保存(“/ tmp /δ/事件”)

df。寫。格式(“δ”)。模式(“覆蓋”)。選項(“replaceWhere”,“start_date > = ' 2017-01-01 '和end_date < = 2017-01-31”)。保存(“/ tmp /δ/事件”)

火花。相依。集(“spark.databricks.delta.replaceWhere.constraintCheck.enabled”,假)

火花。相依。集(“spark.databricks.delta.replaceWhere.constraintCheck.enabled”,假)

df。寫\。格式(“δ”)\。模式(“覆蓋”)\。選項(“replaceWhere”,“生日> = ' 2017-01-01 '和生日< = 2017-01-31”)\。保存(“/ tmp /δ/ people10m”)

df。寫。格式(“δ”)。模式(“覆蓋”)。選項(“replaceWhere”,“生日> = ' 2017-01-01 '和生日< = 2017-01-31”)。保存(“/ tmp /δ/ people10m”)

火花。相依。集(“spark.databricks.delta.replaceWhere.dataColumns.enabled”,假)

火花。相依。集(“spark.databricks.delta.replaceWhere.dataColumns.enabled”,假)

集火花。sql。來源。partitionOverwriteMode=動態;插入覆蓋表默認的。people10m選擇*從更多人;

df。寫\。格式(“δ”)\。模式(“覆蓋”)\。選項(“partitionOverwriteMode”,“動態”)\。saveAsTable(“default.people10m”)

df。寫。格式(“δ”)。模式(“覆蓋”)。選項(“partitionOverwriteMode”,“動態”)。saveAsTable(“default.people10m”)

df。寫。格式(“δ”)\。模式(“添加”)\。選項(“maxRecordsPerFile”,“10000”)\。保存(“/ tmp /δ/ people10m”)

df。寫。格式(“δ”)。模式(“添加”)。選項(“maxRecordsPerFile”,“10000”)。保存(“/ tmp /δ/ people10m”)

app_id=…#一個獨一無二的字符串用作應用程序ID。版本=…#一個單調遞增數字,充當事務的版本。dataFrame。寫。格式(…)。選項(“txnVersion”,版本)。選項(“txnAppId”,app_id)。保存(…)

瓦爾appId=…/ /一個獨一無二的字符串用作應用程序ID。版本=…/ /一個單調遞增數字,充當事務的版本。dataFrame。寫。格式(…)。選項(“txnVersion”,版本)。選項(“txnAppId”,appId)。保存(…)

集火花。磚。δ。commitInfo。userMetadata=覆蓋- - - - - -為- - - - - -修複- - - - - -不正確的- - - - - -數據插入覆蓋默認的。people10m選擇*從更多人

df。寫。格式(“δ”)\。模式(“覆蓋”)\。選項(“userMetadata”,“overwritten-for-fixing-incorrect-data”)\。保存(“/ tmp /δ/ people10m”)

df。寫。格式(“δ”)。模式(“覆蓋”)。選項(“userMetadata”,“overwritten-for-fixing-incorrect-data”)。保存(“/ tmp /δ/ people10m”)

模式驗證

三角洲湖自動驗證的模式DataFrame寫入表的兼容模式。三角洲湖使用以下規則來確定是否寫從DataFrame表兼容:

• 所有DataFrame列必須存在於目標表。如果有列DataFrame表中不存在,會拋出一個異常。列在表而不是DataFrame設置為null。

• DataFrame列的數據類型必須匹配目標表的列數據類型。如果它們不匹配,會拋出一個異常。

• DataFrame列名不能隻有情況不同。這意味著您不能列如“Foo”和“Foo”定義在相同的表。雖然您可以使用火花,以防敏感或不敏感(默認)模式,拚花是大小寫敏感的,當存儲和返回列信息。三角洲湖case-preserving但不敏感當存儲模式,這一限製,以避免潛在的錯誤,數據損壞或損失的問題。

三角洲湖支持DDL顯式地添加新列和自動更新模式的能力。

如果您指定其他選項,例如partitionBy,結合附加模式,三角洲湖驗證他們為任何不匹配匹配和拋出一個錯誤。當partitionBy不存在,添加自動跟隨現有數據的分區。

關於執行和發展模式的更多信息在三角洲湖,看這個YouTube視頻(55分鍾)。

更新表模式

三角洲湖允許您更新一個表的模式。支持以下類型的變化:

• 添加新列(在任意位置)

• 重新安排現有的列

• 重命名現有列

你可以讓這些變化顯式或隱式地使用DML使用DDL。

推薦的方法,請參閱生產磚上考慮結構的流媒體應用程序。

改變表table_name添加列(col_namedata_type(評論col_comment](第一個|後colA_name),…)

改變表table_name添加列(col_name。nested_col_namedata_type(評論col_comment](第一個|後colA_name),…)

- - - - - -根|- - - - - -可樂|- - - - - -colB|+ -field1|+ -field2

- - - - - -根|- - - - - -可樂|- - - - - -colB|+ -field1|+ -嵌套的|+ -field2

改變表table_name改變(列]col_namecol_namedata_type(評論col_comment](第一個|後colA_name]

改變表table_name改變(列]col_name。nested_col_namenested_col_namedata_type(評論col_comment](第一個|後colA_name]

- - - - - -根|- - - - - -可樂|- - - - - -colB|+ -field1|+ -field2

- - - - - -根|- - - - - -可樂|- - - - - -colB|+ -field2|+ -field1

改變表table_name取代列(col_name1col_type1(評論col_comment1),…)

改變表盒子取代列(colC字符串,colB結構體<field2:字符串,嵌套的:字符串,field1:字符串>,可樂字符串)

- - - - - -根|- - - - - -可樂|- - - - - -colB|+ -field1|+ -field2

- - - - - -根|- - - - - -colC|- - - - - -colB|+ -field2|+ -嵌套的|+ -field1|- - - - - -可樂

改變表table_name重命名列old_col_name來new_col_name

改變表table_name重命名列col_name。old_nested_field來new_nested_field

改變表盒子重命名列colB。field1來field001

- - - - - -根|- - - - - -可樂|- - - - - -colB|+ -field1|+ -field2

- - - - - -根|- - - - - -可樂|- - - - - -colB|+ -field001|+ -field2

改變表table_name下降列col_name

改變表table_name下降列(col_name_1,col_name_2)

火花。讀。表(…)\。withColumn(“生日”,上校(“生日”)。投(“日期”))\。寫\。格式(“δ”)\。模式(“覆蓋”)。選項(“overwriteSchema”,“真正的”)\。saveAsTable(…)

火花。讀。表(…)\。withColumnRenamed(“dateOfBirth”,“生日”)\。寫\。格式(“δ”)\。模式(“覆蓋”)\。選項(“overwriteSchema”,“真正的”)\。saveAsTable(…)

替換表模式

默認情況下,覆蓋一個表中的數據不會覆蓋模式。當覆蓋表使用模式(“覆蓋”)沒有replaceWhere,您可能還想覆蓋寫入數據的模式。你替換的模式和分區表通過設置overwriteSchema選項真正的:

df。寫。選項(“overwriteSchema”,“真正的”)

表意見

三角洲湖支持創建視圖的三角洲表就像你可能會與一個數據源表。

這些視圖集成訪問控製表允許列和行級安全。

當你和視圖操作的核心挑戰是解決模式。如果你改變一個增量表模式,你必須重新創建導數觀點占任何添加模式。例如,如果你添加一個新的列到三角洲表,您必須確保這個專欄有適當的基表的視圖之上。

表屬性

你可以存儲自己的元數據表屬性使用TBLPROPERTIES在創建和改變。然後,您可以顯示元數據。例如:

改變表默認的。people10m集TBLPROPERTIES(“部門”=“會計”,“delta.appendOnly”=“真正的”);——顯示表的屬性。顯示TBLPROPERTIES默認的。people10m;——顯示“部門”表屬性。顯示TBLPROPERTIES默認的。people10m(“部門”);

TBLPROPERTIES被存儲為δ表元數據的一部分。你不能定義新的TBLPROPERTIES在一個創建聲明如果δ表已經存在於一個給定的位置。

此外,定製的行為和性能,支持某些湖δ表屬性:

• 在三角洲表塊刪除和更新:delta.appendOnly = true。

• 配置時間旅行保留屬性:delta.logRetentionDuration = < interval-string >和delta.deletedFileRetentionDuration = < interval-string >。有關詳細信息,請參見數據保留。

• 配置收集統計數據的列數:delta.dataSkippingNumIndexedCols = n。這個屬性表示統計數據收集的作家隻有第一n表中的列。也跳過代碼忽略了統計數據以外的任何列這一列索引。這個屬性將隻影響新寫入的數據。

• 隨機文件的前綴,以避免熱點在S3中元數據:delta.randomizeFilePrefixes = true。需要大量的表(成千上萬的每秒請求)的快速讀/寫操作,我們強烈建議奉獻一個S3 bucket表(定位表的根源桶),並使隨機文件前綴來得到最好的體驗。

你也可以設置三角洲。前綴的屬性在第一次提交到三角洲表使用火花配置。例如,初始化一個增量表的屬性delta.appendOnly = true,設置火花配置spark.databricks.delta.properties.defaults.appendOnly來真正的。例如:

火花。sql(“設置spark.databricks.delta.properties.defaults.appendOnly = true”)

火花。相依。集(“spark.databricks.delta.properties.defaults.appendOnly”,“真正的”)

火花。相依。集(“spark.databricks.delta.properties.defaults.appendOnly”,“真正的”)

看到也三角洲表屬性引用。

表元數據

三角洲湖有豐富的功能為探索表元數據。

它支持顯示(分區|列)和描述表。看到

• 磚運行時7。x,上圖:顯示分區,顯示列,描述表

它還提供了獨特的命令如下:

配置存儲憑證

三角洲湖使用Hadoop文件係統api來訪問存儲係統。存儲係統的credentails通常可以通過Hadoop設置配置。三角洲湖Hadoop提供了多種方式來組配置類似於Apache火花。

df1=火花。讀。格式(“δ”)\。選項(“fs.s3a.access.key”,“< access-key-1 >”)\。選項(“fs.s3a.secret.key”,“< secret-key-1 >”)\。讀(“…”)df2=火花。讀。格式(“δ”)\。選項(“fs.s3a.access.key”,“< access-key-1 >”)\。選項(“fs.s3a.secret.key”,“< secret-key-2 >”)\。讀(“…”)df1。聯盟(df2)。寫。格式(“δ”)\。模式(“覆蓋”)\。選項(“fs.s3a.access.key”,“< access-key-3 >”)\。選項(“fs.s3a.secret.key”,“< secret-key-3 >”)\。保存(“…”)

瓦爾df1=火花。讀。格式(“δ”)。選項(“fs.s3a.access.key”,“< access-key-1 >”)。選項(“fs.s3a.secret.key”,“< secret-key-1 >”)。讀(“…”)瓦爾df2=火花。讀。格式(“δ”)。選項(“fs.s3a.access.key”,“< access-key-2 >”)。選項(“fs.s3a.secret.key”,“< secret-key-2 >”)。讀(“…”)df1。聯盟(df2)。寫。格式(“δ”)。模式(“覆蓋”)。選項(“fs.s3a.access.key”,“< access-key-3 >”)。選項(“fs.s3a.secret.key”,“< secret-key-3 >”)。保存(“…”)

筆記本

例如各種三角洲表的元數據命令,見以下筆記本的終結: