脚本宝典收集整理的这篇文章主要介绍了

Hive ACID 测试

脚本宝典小编觉得挺不错的,现在分享给大家,也给大家做个参考,希望能帮助你少写一行代码,多一份安全和惬意。

在 Hive 0.14 之前,Hive QL 一直不支持insert、update、delete 操作,这显然很不方便,尤其是在构建数据仓库的过程中,一个比较常见的例子是维度表经常需要更新某列,在 Hive 中需要更新历史所有数据,这显然是不合理的。

在 Hive 0.14 版本,支持了行级别的 ACID 与 Transactions,这也就解决了上面的问题。

本文主要讲解如何在 Hive 0.14 配置,使得支持上述两个特性,相关原理及性能分析后面再单独写一篇文章来分析。

相关软件说明:

  • Hive 1.1.0

  • mysql 5.6(存储 metastore )

1. 初始化数据库

mysql> create database metastore_db; mysql> USE metastore_db;   mysql> SOURCE $HIVE_HOME/scripts/metastore/upgrade/mysql/hive-schema-1.1.0.mysql.sql mysql> SOURCE $HIVE_HOME/scripts/metastore/upgrade/mysql/hive-txn-schema-0.13.0.mysql.sql mysql> CREATE USER 'hive'@'localhost' IDENTIFIED BY 'mypassword';   mysql> grant all privileges on metastore_db.* to hive@'%' identified by 'hive'; mysql> FLUSH PRIVILEGES;   mysql> quit;  

这里需要注意的是,必须source txn相关的表,否则后面创建表是会报下面几种错误:

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:javax.jdo.JDODataStoreException: Schema Transaction threw exception "Add classes to Catalog "metastore_db", Schema """     at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:451)     at org.datanucleus.api.jdo.JDOPersistenceManager.jdoMakePersistent(JDOPersistenceManager.java:732)     at org.datanucleus.api.jdo.JDOPersistenceManager.makePersistent(JDOPersistenceManager.java:752)     at org.apache.hadoop.hive.metastore.ObjectStore.createTable(ObjectStore.java:794)     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)     at java.lang.reflect.Method.invoke(Method.java:497)     at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:98)     at com.sun.proxy.$Proxy6.createTable(Unknown Source)     at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_core(HiveMetaStore.java:1393)     at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_with_environment_context(HiveMetaStore.java:1426)

2. 配置 hive-site.xml

<property>     <name>hive.support.concurrency</name>     <value>true</value> </property> <property>     <name>hive.txn.manager</name>     <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value> </property> <property>     <name>hive.enforce.bucketing</name>     <value>true</value> </property> <property>     <name>hive.compactor.initiator.on</name>     <value>true</value> </property> <property>     <name>hive.compactor.worker.threads</name>     <value>2</value> </property> <property>     <name>hive.exec.dynamic.partition.mode</name>     <value>nonstrict</value> </property> <property>     <name>hive.compactor.initiator.on</name>     <value>true</value> </property>  <!-- mysql 相关 --> <property>      <name>javax.jdo.option.ConnectionURL</name>      <value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>       <description>Enter your JDBC connection string. </description>   </property>   <property>       <name>javax.jdo.option.ConnectionDriverName</name>       <value>com.mysql.jdbc.Driver</value>   </property>   <property>       <name>javax.jdo.option.ConnectionUserName</name>       <value>hive</value> </property>   <property>       <name>javax.jdo.option.ConnectionPassword</name>       <value>hive</value>   </property>   <property>       <name>datanucleus.autoCreateSchema</name>       <value>false</value>   </property>   <property>       <name>datanucleus.fixedDatastore</name>       <value>true</value>   </property> 

3. 创建具备ACID及Transactions的表

这里的表需要具备下面几个条件:

  1. 必须以 ORC 格式存储

  2. 必须分 bucket,且不能 sort

  3. 必须显式声明transations

大致如下面模版所述:

create table T(...)  clustered by (a) into 2 buckets stored as orc TBLPROPERTIES ('transactional'='true');

知道了限定条件,我们来创建一个表做个测试:

create table goods_info (     shop_id int,     created_time string,     title string ) clustered by (shop_id) into 3 buckets stored as orc TBLPROPERTIES ('transactional'='true');

之后insert两条数据

insert into table goods_info values (1, '2016-04-02 21:00:00', 'HIVE 7天入门'), (2, '2016-04-02 21:01:00', 'SICP');

回车后,会运行一个MR去插入数据,之后就可以查询了

hive (default)> select * from goods_info; OK 1    2016-04-02 21:00:00    HIVE 7)e� 2    2016-04-02 21:01:00    SICP Time taken: 0.116 seconds, Fetched: 2 row(s)

这里的中文貌似有乱码问题,这里先不做处理了。
在 hdfs 上生产的文件为:

210  /user/hive/warehouse/goods_info/delta_0000005_0000005/bucket_00000 810  /user/hive/warehouse/goods_info/delta_0000005_0000005/bucket_00001 781  /user/hive/warehouse/goods_info/delta_0000005_0000005/bucket_00002

在创建表时,我遇到了下面的问题

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:javax.jdo.JDODataStoreException: An exception was thrown while adding/validating class(es) : Specified key was too long; max key length is 767 bytes com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes     at sun.reflect.GeneratedConstructorAccessor31.newInstance(Unknown Source)     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)     at java.lang.reflect.Constructor.newInstance(Constructor.java:422)     at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)     at com.mysql.jdbc.Util.getInstance(Util.java:360)     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)     at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)     at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:848)     at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:742)     at com.jolbox.bonecp.StatementHandle.execute(StatementHandle.java:254)

这时由于 mysql 自身对索引字段字符长度的限制,我数据库用的是utf8编码,767/3=255.6,这也就是说索引字段的varchar不能大于255,对于 mysql5.6以及以后的版本,可以通过在/my/etc.conf中添加如下配置解决

[mysqld] innodb_file_format=BARRACUDA innodb_large_prefix=on innodb_file_per_table=true

相关原理可见:

总结

以上是脚本宝典为你收集整理的

Hive ACID 测试

全部内容,希望文章能够帮你解决

Hive ACID 测试

所遇到的程序开发问题,欢迎加入QQ群277859234一起讨论学习。如果觉得脚本宝典网站内容还不错,欢迎将脚本宝典网站推荐给程序员好友。 本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。

80%的人都看过