如何針對數個 Apache Hive 中繼存放區問題進行疑難排解How to troubleshoot several Apache Hive metastore problems

問題1:外部中繼存放區資料表無法使用Problem 1: External metastore tables not available

當您檢查驅動程式記錄檔時,您會看到包含錯誤的堆疊追蹤 Required table missingWhen you inspect the driver logs, you see a stack trace that includes the error Required table missing:

WARN Query: Query for candidates of org.apache.hadoop.hive.metastore.model.MDatabase and subclasses resulted in no possible candidates

Required table missing: "DBS" in Catalog "" Schema "". DataNucleus requires this table to perform its
persistence operations. Either your MetaData is incorrect, or you need to enable
"datanucleus.schema.autoCreateTables"

org.datanucleus.store.rdbms.exceptions.MissingTableException: Required table missing : "DBS" in Catalog ""  Schema "". DataNucleus requires this table to perform its persistence operations. Either your MetaData is incorrect, or you need to enable
"datanucleus.schema.autoCreateTables"

   at

org.datanucleus.store.rdbms.table.AbstractTable.exists(AbstractTable.java:606)

   at

org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.performTablesValidation(RDBMSStoreManager.java:33
85)

原因Cause

資料庫存在,但沒有中繼存放區資料表。The database is present, but there are no metastore tables.

解決方法Solution

如果外部中繼存放區版本是 Hive 2.0 或以上,請使用Hive 架構工具來建立中繼存放區資料表。If the external metastore version is Hive 2.0 or above, use the Hive Schema Tool to create the metastore tables. 針對 Hive 2.0 以下的版本,在現有的 init 腳本中新增具有下列設定的中繼存放區資料表:For versions below Hive 2.0, add the metastore tables with the following configurations in your existing init script:

spark.hadoop.datanucleus.autoCreateSchema=true
spark.hadoop.datanucleus.fixedDatastore=false

您也可以直接在 Apache Spark設定中設定這些設定:You can also set these configurations in the Apache Spark configuration directly:

datanucleus.autoCreateSchema true
datanucleus.fixedDatastore false

問題2: Hive 中繼存放區驗證失敗Problem 2: Hive metastore verification failed

當您檢查驅動程式記錄檔時,您會看到包含錯誤的堆疊追蹤,如下所示:When you inspect the driver logs, you see a stack trace that includes an error like the following:

18/09/24 14:51:07 ERROR RetryingHMSHandler: HMSHandler Fatal error:
MetaException(message:Version information not found in metastore. )

   at
org.apache.hadoop.hive.metastore.ObjectStore.checkSchema(ObjectStore
.java:7564)

   at
org.apache.hadoop.hive.metastore.ObjectStore.verifySchema(ObjectStore.
java:7542)

   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

原因Cause

VERSION中繼存放區中的資料表是空的。The VERSION table in the metastore is empty.

解決方法Solution

執行下列其中一個動作:Do one of the following:

  • 使用查詢,在資料表中填入 VERSION 正確的版本值 INSERTPopulate the VERSION table with the correct version values using an INSERT query.

  • 設定下列設定,以在叢集的 Spark 設定中關閉中繼存放區驗證:Set the following configurations to turn off the metastore verification in the Spark configuration of the cluster:

    hive.metastore.schema.verification false
    hive.metastore.schema.verification.record.version false
    

問題3:超過中繼存放區連接限制Problem 3: Metastore connection limit exceeded

在叢集上執行的命令會失敗,並在驅動程式記錄檔中出現下列堆疊追蹤:Commands run on the cluster fail with the following stack trace in the driver logs:

Unable to open a test connection to the given
database. JDBC url =
jdbc:<jdbcURL>?trustServerCertificate=true&useSS
L=true, username = <REDACTED>. Terminating
connection pool (set lazyInit to true if you
expect to start your database after your app).
Original Exception: ------

java.sql.SQLSyntaxErrorException: User
'<userId>' has exceeded the
'max_user_connections' resource (current value:
100)
at
org.mariadb.jdbc.internal.util.exceptions.Except
ionMapper.get(ExceptionMapper.java:163)
at
org.mariadb.jdbc.internal.util.exceptions.Except
ionMapper.getException(ExceptionMapper.java:106)
at
org.mariadb.jdbc.internal.protocol.AbstractConne
ctProtocol.connectWithoutProxy(AbstractConnectPr
otocol.java:1036)

原因Cause

中繼存放區設定只允許100連接。The metastore configuration allows only 100 connections. 當達到連接限制時,不允許新的連接,且命令會失敗,並出現此錯誤。When the connection limit is reached, new connections are not allowed, and commands fail with this error. Azure Databricks 工作區中的每個叢集都會建立與中繼存放區的連線。Each cluster in the Azure Databricks workspace establishes a connection with the metastore. 如果您有大量執行的叢集,則可能會發生此問題。If you have a large number of clusters running, then this issue can occur. 此外,不正確的設定可能會導致連線流失,導致連線數目持續增加,直到達到限制為止。Additionally, incorrect configurations can cause a connection leak, causing the number of connections to keep increasing until the limit is reached.

解決方法Solution

請使用下列其中一個動作來修正問題:Correct the problem with one of the following actions:

  • 如果您使用外部中繼存放區,而且您有大量執行的叢集,請增加外部中繼存放區的連接限制。If you are using an external metastore and you have a large number of clusters running, then increase the connection limit on your external metastore.
  • 如果您不是使用外部中繼存放區,請確定您的叢集上沒有任何自訂的 Hive 中繼存放區設定。If you are not using an external metastore, ensure that you do not have any custom Hive metastore configurations on your cluster. 使用 Azure Databricks 所提供的中繼存放區時,您應該在叢集上使用 Hive 中繼存放區的預設設定。When using the metastore provided by Azure Databricks, you should use the default configurations on the cluster for the Hive metastore.
  • 如果您使用預設設定,但仍遇到此問題,請洽詢 Azure Databricks 支援。If you are using the default configuration and still encounter this issue, contact Azure Databricks Support. 視 Azure Databricks 工作區的設定而定,您可能會增加允許的內部中繼存放區連接數目。Depending on the configuration of your Azure Databricks workspace, it might be possible to increase the number of connections allowed to the internal metastore.

問題4:資料表動作失敗,因為資料行有太多的中繼資料Problem 4: Table actions fail because column has too much metadata

當單一資料行的中繼資料數量超過4000個字元時,資料表動作會失敗,並出現類似如下的錯誤:When the quantity of metadata for a single column exceeds 4000 characters, table actions fail with an error like this:

Error in SQL statement: IllegalArgumentException:
Error: type expected at the position 3998 of 'struct<num_ad_accounts:bigint,num_benchmarks:bigint,num_days_range:string,num_days_in_history:string,num_fb_pages:bigint,num_g_profiles:bigint,num_ga_views:bigint,num_groups:bigint,num_ig_profiles:bigint,num_li_pages:bigint,num_labels:string,num_labels_added:bigint,num_labels_

原因Cause

這是 Hive 中繼存放區版本2.3.0 (hive-12274)中已修正的 bug。This is a bug that was fixed in Hive Metastore version 2.3.0 (HIVE-12274). Azure Databricks 使用舊版的 Hive 中繼存放區(版本0.13),因此當資料行有太多的中繼資料(例如匯入的 JSON 架構)時,就會發生這個錯誤。Azure Databricks uses an earlier version of Hive Metastore (version 0.13), so this bug occurs when there is too much metadata for a column, such as an imported JSON schema.

解決方法Solution

因應措施是設定使用2.3.0 或更新版本的外部 Hive 中繼存放區As a workaround, set up an external Hive metastore that uses version 2.3.0 or above. 然後使用下列命令來刪除現有的資料表:Then delete the existing table with the following command:

spark.sessionState
  .catalog
  .externalCatalog
  .dropTable("default", "test_table_tabledrop_1", ignoreIfNotExists = false, purge = false)