把hive 的metadata配置成mysql,在mysql数据库里创建了hive db后,修改hive的conf目录下的hive-site.xml为:
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
<configuration>
<!-- WARNING!!! This file is provided for documentation purposes ONLY! -->
<!-- WARNING!!! Any changes you make to this file will be ignored by Hive. -->
<!-- WARNING!!! You must make your changes in hive-site.xml instead. -->
<!-- Hive Execution Parameters -->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://127.0.0.1:3306/hive?createDatabaseIfNotExist=true</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
<description>username to use against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>root</value>
<description>password to use against metastore database</description>
</property>
</configuration>
并且把mysql-connector-java-5.1.18-bin.jar拷到hive的lib目录下面.
结果运行hive, create table:
hive>
> create table user_info(userid int, user_name string);
FAILED: Error in metadata: 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
...
查看hive 的log:
vi /tmp/root/hive.log
12 2014-02-13 16:48:59,910 INFO ql.Driver (PerfLogger.java:PerfLogEnd(115)) - </PERFLOG method=TimeToSubmit start=1392281339891 end=1392281339910 duration=19>
13 2014-02-13 16:48:59,968 ERROR DataNucleus.Datastore (Log4JLogger.java:error(115)) - Error thrown executing CREATE TABLE `SD_PARAMS`
14 (
15 `SD_ID` BIGINT NOT NULL,
16 `PARAM_KEY` VARCHAR(256) BINARY NOT NULL,
17 `PARAM_VALUE` VARCHAR(4000) BINARY NULL,
18 CONSTRAINT `SD_PARAMS_PK` PRIMARY KEY (`SD_ID`,`PARAM_KEY`)
19 ) ENGINE=INNODB : Specified key was too long; max key length is 767 bytes
20 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
搜了下,在mysql上执行:
在mysql机器的上运行:
alter database hive character set latin1;
问题解决.
hive>
>
> create table user_info(userid int, user_name string);
OK
Time taken: 0.593 seconds
hive> show tables;
OK
user_info
Time taken: 0.451 seconds
hive>
> load data local inpath "/u/opt/hive-0.10.0-cdh4.5.0/examples/files/kv1.txt" overwrite into table user_info;
Copying data from file:/u/opt/hive-0.10.0-cdh4.5.0/examples/files/kv1.txt
Copying file: file:/u/opt/hive-0.10.0-cdh4.5.0/examples/files/kv1.txt
Loading data to table default.user_info
Table default.user_info stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 5812, raw_data_size: 0]
OK
Time taken: 0.691 seconds
hive>
> select * from user_info limit 10;
OK
238 val_238
86 val_86
311 val_311
27 val_27
165 val_165
409 val_409
255 val_255
278 val_278
98 val_98
484 val_484
Time taken: 0.262 seconds
hive>
相关推荐
hive建表语句hive建表语句hive建表语句hive建表语句hive建表语句hive建表语句
java根据excel生成 hive建表语句 可以根据自己的格式修改
生成hive建表语句
基于oracle或者mysql生成自动建表同步源数据表结构及注释
mysql数据抽取,自动生成hive建表语句,只需要输入表名即可
hive分桶,分区操作案例实战。学习的好资料。hive分桶,分区操作案例实战。学习的好资料。hive分桶,分区操作案例实战。学习的好资料。
Hive表生成工具,Hive表生成工具Hive表生成工具
根据Excel记录生成Mysql和Hive建表语句
本文主要给大家介绍了关于Mysql元数据生成Hive建表语句注释脚本的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍: 最近在将数据从Mysql 等其他关系型数据库 抽取到Hive 表中时,需要同步...
可以从hive元数据生成建表语句的资源。包括表结构、分区等信息
Hive常见异常处理,长期累积的经验 Hive常见异常处理
excel动态创建hive表语句模板
今天更新hive版本的时候,把关联的数据库删掉了,重新生成,之后出现MetaException(message:An exception was thrown while adding/validating class(es) : Column length too big for column 'PARAM_VALUE' (max = ...
在excel模板中填写自己表的字段英文名、字段类型、字段中文名、分区英文名、分区数据类型、分区中文名、表英文名、表中文名,自动生成建表语句和查询语句,提高日常数据效率。 前提:本机安装java1.7,并且配置了...
Hive的建表SQL
根据excel中表的字段清单,批量生成建表语句; 打开工具,按格式填入表名、字段、类型等内容,点击“视图”-->“宏”-->“查看宏”-->“执行”
大数据工具之HIVE外键及多表关联的介绍.zip
4.1 Hive 建表: 3 4.1.1使用LIKE关键字创建一个与已有表模式相同的新表: 4 4.2 Hive 修改表 4 4.2.1 Hive 新增一个字段: 4 4.2.2 Hive 修改字段名/字段类型/字段位置/字段注释: 4 4.2.3 Hive 重命名表名: 4 4.3...
本项目对如何在Springboot项目中整合hive-jdbc进行简单示例和介绍,亲测可用,请放心下载。