hive 在Spark上自动创建配置单元元存储

yvt65v4c 于 2022-11-05 发布在 Hive

关注(0)|答案(1)|浏览(271)

我正在尝试创建一个hive元存储，并将其托管在spark的mysql中。hive-site.xml如下所示：

>>> -- hive-site.xml --- >>>>>>>>>>>>>>>>>>
<configuration>
  <property>
     <name>javax.jdo.option.ConnectionURL</name>
     <value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>
   </property>
   <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>hive</value>
    <description>Username to use against metastore database</description>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>xxxxxx</value>
    <description>password to use against metastore database</description>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.cj.jdbc.Driver</value>
  </property>
  <property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/var/lib/hive/hive-warehouse</value>
    <description>Hive Metastore location</description>
  </property>
  <property>
    <name>datanucleus.autoCreateSchema</name>
    <value>false</value>
  </property>
  <property>
    <name>datanucleus.fixedDatastore</name>
    <value>true</value>
  </property>
  <property>
    <name>datanucleus.autoStartMechanism</name>
    <value>SchemaTable</value>
  </property>
  <property>
    <name>hive.metastore.schema.verification</name>
    <value>true</value>
  </property>
  <property>
    <name>datanucleus.schema.autoCreateTables</name>
    <value>true</value>
  </property>
</configuration>
<<< --- hive-site.xml --- <<<<<<<<<<<<<<<<<

当我从Spark（3.1.1）启动thrift服务（start-thriftserver.sh）时出现错误消息

...
    21/12/16 18:03:58 INFO Persistence: Property datanucleus.cache.level2 unknown - will be ignored
21/12/16 18:03:59 INFO BlockManagerMasterEndpoint: Registering block manager 192.168.45.28:27131 with 366.3 MiB RAM, BlockManagerId(0, 192.168.45.28, 27131, None)
21/12/16 18:04:19 WARN Datastore: SQL Warning : 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
21/12/16 18:04:19 WARN Datastore: SQL Warning : 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
21/12/16 18:04:19 WARN Datastore: SQL Warning : 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
21/12/16 18:04:19 WARN Datastore: SQL Warning : 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
21/12/16 18:04:19 WARN Datastore: SQL Warning : 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
21/12/16 18:04:19 WARN Datastore: SQL Warning : 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
21/12/16 18:04:20 INFO ObjectStore: Setting MetaStore object pin classes with hive.metastore.cache.pinobjtypes="Table,StorageDescriptor,SerDeInfo,Partition,Database,Type,FieldSchema,Order"
21/12/16 18:04:33 WARN Datastore: SQL Warning : 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
21/12/16 18:04:33 WARN Datastore: SQL Warning : 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
21/12/16 18:04:33 WARN Datastore: SQL Warning : 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
21/12/16 18:04:33 WARN Datastore: SQL Warning : 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
21/12/16 18:04:33 WARN Datastore: SQL Warning : 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
21/12/16 18:04:40 WARN Datastore: SQL Warning : 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
21/12/16 18:04:40 WARN Datastore: SQL Warning : 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
21/12/16 18:04:58 ERROR Datastore: Error thrown executing CREATE TABLE `TBLS`
(
    `TBL_ID` BIGINT NOT NULL,
    `CREATE_TIME` INTEGER NOT NULL,
    `DB_ID` BIGINT NULL,
    `LAST_ACCESS_TIME` INTEGER NOT NULL,
    `OWNER` VARCHAR(767) BINARY NULL,
    `RETENTION` INTEGER NOT NULL,
    `IS_REWRITE_ENABLED` BIT NOT NULL,
    `SD_ID` BIGINT NULL,
    `TBL_NAME` VARCHAR(256) BINARY NULL,
    `TBL_TYPE` VARCHAR(128) BINARY NULL,
    `VIEW_EXPANDED_TEXT` TEXT [CHARACTER SET charset_name] [COLLATE collation_name] NULL,
    `VIEW_ORIGINAL_TEXT` TEXT [CHARACTER SET charset_name] [COLLATE collation_name] NULL,
    CONSTRAINT `TBLS_PK` PRIMARY KEY (`TBL_ID`)
) ENGINE=INNODB : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[CHARACTER SET charset_name] [COLLATE collation_name] NULL,
    `VIEW_ORIGINAL_T' at line 13
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[CHARACTER SET charset_name] [COLLATE collation_name] NULL,
    `VIEW_ORIGINAL_T' at line 13
...

在发布之前，我尝试删除mysql（8.0.25）的数据库，为数据库重新创建不同的字符集/排序规则，但没有成功：

0900_ai_ci中指定的值
utf8 mb 4/utf8 mb 4通用字符集
1.二进制/二进制
我不确定它的东西发生在metastore模式自动创建或东西，我可以为mysql做调整？

Hive

来源：https://stackoverflow.com/questions/70387971/hive-metastore-autocreate-on-spark

1条答案

按热度按时间

fae0ux8s1#

获得同样的问题，而我做的Java代码与Mysql 8版本

public class HiveMetaStore {
public static void main(String[] args) {
    String warehouseLocation = new File("spark-warehouse").getAbsolutePath();
    SparkSession spark = SparkSession
            .builder()
            .master("local[4]")
            .appName("HiveMetaStore")
            .config("spark.sql.warehouse.dir", warehouseLocation)
            .config("spark.hadoop.javax.jdo.option.ConnectionURL", "jdbc:mysql://localhost:3306/metastore_db")
            .config("spark.hadoop.javax.jdo.option.ConnectionDriverName", "com.mysql.cj.jdbc.Driver")
            .config("spark.hadoop.javax.jdo.option.ConnectionUserName", "root")
            .config("spark.hadoop.javax.jdo.option.ConnectionPassword", "root")
            .config("spark.sql.hive.metastore.version", "2.3.9")
            .config("datanucleus.schema.autoCreateAll", true)
            .enableHiveSupport()
            .getOrCreate();
    spark.catalog().listDatabases().show(false);      
}

}

赞(0）回复(0）举报 2022-11-05

我来回答

hive 在Spark上自动创建配置单元元存储

1条答案

相关问题

热门标签

最新问答