hive日期格式化的问题 问题　　在hive中，经常需要进行日期的计算，可是，经常会出现这样一种情况，hive仓库中日期的存储格式是yyyyMMdd,例如20220505，在进行日期的计算时，hive支持的函数例如，add_months 仅识别yyyy-MM-dd格式的函数，如果输入yyyyMMdd的参数，返回结果是null。所以我们在对日期的字段进行计算时，优先需要进行格式的转换，将yyyyMMdd转换成yyyy-MM-dd格式。需要注意的是，字符串日期格式化仅支持yyyy-MM-dd转为yyyyMMdd，不支持yyyyMMdd转为yyyy-MM-dd，转化后为null。解决　　解决日期格式化最好的方法就是将日期格式转化为时间戳，再转换成需要的格式举例：将yyyyMMdd HH:mm:ss格式的字符串时间 20220505 05:05:05 转换为yyyy-MM-dd HH:mm:ss 格式 2022-05-05 05:05:05from_unixtime(unix_timestamp('20220505 05:05:05','yyyyMMdd HH:mm:ss'),'yyyy-MM-dd HH:mm:ss')

导入数据到hive的字段含有转义符问题 问题　　从mysql中抽取数据到hive中发现数据错乱了，mysql中的两百条数据抽到hive中变成了九千多条。初步怀疑是建表时的分割符问题，更换行分隔符和列分割符都无法解决问题。后面查看mysql中数据，发现有个字段中包含大量换行符、制表符、回车等，问题找到了，开始解决解决　　Hive数据处理中去除字段中的换行符、分割符需要使用regexp_replace()函数具体使用方式如下：regexp_replace(字段,需要替换的符号,替换符号)举例：mysql数据：test表中字段field_B中含有换行符等符号，需要在抽取时进行处理field_Afield_Bfield_Caaa\t\n\001\bxxxaaabbb\t\n\001\bxxxbbbccc\t\n\001\bxxxbbbhive建表语句：create table if not exists test( field_A string, field_B string, field_C string) partitioned by (date timestamp) row format delimited fields terminated by ',';抽取语句：insert overwrite table partition (date = current_date()) select field_A, regexp_replace(field_B,'\\n|\\t|\\r',''), field_C from test

HadoopHA模式下Hive启动报错 报错hadoop配置HA后启动hive报错Exception in thread "main" java.lang.IllegalArgumentException: java.net.UnknownHostException: hadoopcluster[root@master hive]# hive which: no hbase in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/src/jdk/bin:/usr/local/src/hadoop/bin:/usr/local/src/hadoop/sbin:/usr/local/src/zookeeper/bin:/usr/local/src/hive/bin:/usr/local/src/scala/bin:/usr/local/src/spark/bin:/usr/local/src/sqoop/bin:/usr/local/src/kafka/bin:/usr/local/src/flume/bin:/usr/local/src/flink/bin) SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/usr/local/src/hive/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/local/src/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory] Logging initialized using configuration in jar:file:/usr/local/src/hive/lib/hive-common-2.3.4.jar!/hive-log4j2.properties Async: true Exception in thread "main" java.lang.IllegalArgumentException: java.net.UnknownHostException: hadoopcluster at org.apache.hadoop.security.SecurityUtil.buildTokenService(SecurityUtil.java:377) at org.apache.hadoop.hdfs.NameNodeProxies.createNonHAProxy(NameNodeProxies.java:320) at org.apache.hadoop.hdfs.NameNodeProxies.createProxy(NameNodeProxies.java:176) at org.apache.hadoop.hdfs.DFSClient.<init>(DFSClient.java:687) at org.apache.hadoop.hdfs.DFSClient.<init>(DFSClient.java:628) at org.apache.hadoop.hdfs.DistributedFileSystem.initialize(DistributedFileSystem.java:149) at org.apache.hadoop.fs.FileSystem.createFileSystem(FileSystem.java:2667) at org.apache.hadoop.fs.FileSystem.access$200(FileSystem.java:93) at org.apache.hadoop.fs.FileSystem$Cache.getInternal(FileSystem.java:2701) at org.apache.hadoop.fs.FileSystem$Cache.get(FileSystem.java:2683) at org.apache.hadoop.fs.FileSystem.get(FileSystem.java:372) at org.apache.hadoop.fs.FileSystem.get(FileSystem.java:171) at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:579) at org.apache.hadoop.hive.ql.session.SessionState.beginStart(SessionState.java:549) at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:750) at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:686) 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:498) at org.apache.hadoop.util.RunJar.run(RunJar.java:226) at org.apache.hadoop.util.RunJar.main(RunJar.java:141) Caused by: java.net.UnknownHostException: hadoopcluster ... 22 more原因原因是hadoop配置文件hdfs-site.xml的nameservices的名称为hadoopcluster<!-- 指定hdfs的nameservices名称为hadoopcluster --> <property> <name>dfs.nameservices</name> <value>hadoopcluster</value> </property>而指定HDFS客户端连接active namenode的java类时漏写了client<property> <name>dfs.failover.proxy.provider.hadoopcluster</name> <value>org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider</value> </property>修改为：<property> <name>dfs.client.failover.proxy.provider.hadoopcluster</name> <value>org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider</value> </property>

Hive的DML数据操作 数据导入Load导入将文件中的数据导入（Load）到 Hive 表中语法:hive > load data [local] inpath '数据路径' [overwrite] into table table_name [partition (partcoll=vall,...)];参数详解:load data：加载数据local：表示从本地加载数据到 hive 表，否则从 HDFS 加载数据到 hive 表inpath：表示加载数据的路径overwrite：表示覆盖表中已有的数据，否则表示追加into table：表示加载到具体哪张表实例:1.无分区----加载本地路径数据到表 (overwrite重写)hive > load data local inpath '/data/load_data.txt' overwrite into table tbl_test;2.有分区----加载本地路径数据到表 (overwrite重写)hive > load data local inpath '/data/load_data.txt' overwrite into tbl_test partition (date='20201212');3.无分区----加载HDFS路径数据到表 (与本地相比，少一个local)hive > load data inpath '/tmp/data.txt' overwrite into table tbl_test;4.有分区----加载HDFS路径数据到表 (overwrite重写)hive > load data inpath '/tmp/data.txt' overwrite into table tbl_test partition (date='20201212');Insert导入向表中插入数据（Insert）1.基本插入数据hive > insert into table table_name partition(date='20201212') values(10001,'test');2.基本模式插入（根据单张表查询结果）hive > insert overwrite table table_name partition(date='20201212') select id, name from student where date='20201212';3.多插入模式（根据多张表查询结果）hive > from student insert overwrite table table_name partition(date='20201212') select id, name where date='20201212' insert overwrite table table_name partition(month='201706') select id, name where date='20201212';As Select导入查询语句中创建表并加载数据（As Select）语法：hive > create table table_name(id int) as select id from table_names;Location导入创建表时通过Location指定加载数据路径语法:hive > create table table_name(id int) location '集群数据路径';Import导入向表中装载数据（import）注意：先用export导出后，再将数据导入语法:hive > import table table_name partition(date='20201212') from '/user/hive/warehouse/export/table_name';　数据导出Insert导出1.将查询的结果导出到本地hive > insert overwrite local directory '/datas/export/file' select * from table_name;2.将查询的结果格式化导出到本地hive > insert overwrite local directory '/datas/export/file' row format delimited fields terminated by '\t' select * from table_name;3.将查询的结果导出到HDFS上(没有local)hive > insert overwrite directory '/datas/export/file' row format delimited fields terminated by '\t' select * from student;Hadoop命令导出注意：该命令在hive命令行执行hive > dfs -get /user/hive/warehouse/test/date=20220505/000000_0 /opt/module/datas/export/test.txt;Hive Shell命令导出基本语法：（hive -f/-e 执行语句或者脚本 > file）bin/hive -e 'select * from default.test;' > /opt/module/datas/export/test.txt;Export导出到HDFShive > export table default.test to '/user/hive/warehouse/export/test';清除数据(Truncate)注意：Truncate只能删除管理表，不能删除外部表中数据hive > truncate table table_name;

Hive的库表操作 Hive的库表操作　　hive是类sql的，所以对库表的操作也和sql相似。这里总结一下Hive的DDL数据定义，因为查询语句篇幅太长，就另起一篇记录Hive操作数据库{lamp/}创建数据库　　首先，hive是将HDFS中结构化的数据文件映射成一张数据库表，并提供类SQL的功能。所以，可以将创建数据库理解为是创建一个大文件夹，而创建表则是创建一个存储结构数据的小文件夹1.创建一个数据库，数据库在HDFS 上的默认存储路径是 /user/hive/warehouse/\*.db2.避免要创建的数据库已经存在错误，增加 if not exists 判断。（标准写法）3.创建一个数据库，可以使用 location 指定数据库在 HDFS 上存放的位置 完整的创建数据库指令为：hive> create database [if not exists] <数据库名> [location <HDFS路径>];举例：在HDFS的hive目录下创建Test库hive> create database if not exists Test location '/hive/Test'; //要指定数据库名查询数据库Hive数据库查询常用操作指令1.查看所有数据库hive> show databases; 2.查看以db_开头的数据库hive> show databases like ‘db_*’;3.查看数据库的描述信息hive> desc database 数据库名;4.查看数据库详细的信息hive> desc database extended 数据库名;切换数据库hive> use 数据库名;修改数据库给数据库设置属性值，采用键值对标识这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。语法：hive> alter database <database_name> set dbpropertis ('<property_name>'='<property_value>',..); 删除数据库1.删除空数据库可以直接删除2.删除数据库时，最好采用 if exists 判断数据库是否存在3.如果数据库不为空，可以采用 cascade 命令，强制删除。在删除数据库的时候会将数据库下面的所有表也进行删除。数据库删除后相应的hdfs上的目录也会被删除。hive> drop database [if exists] 数据库名;Hive操作表{lamp/}创建表建表语句：create [external] table [if not exists] 表名 [(字段名 数据类型 [comment 字段注释], ...)] [comment 表注释] [partitioned by (字段名 数据类型 [comment 字段注释], ...)] [clustered by (字段名, 字段名, ...) [sorted by (字段名 [ASC|DESC], ...)] into num_buckets BUCKETS] [row format row_format] [stored as file_format] [location hdfs_path]字段解释：1.create table 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；可以用 if not exists 选项来忽略这个异常2.external 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（location），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据3.comment：为表或列添加注释4.partitioned by 创建分区表5.clustered by 创建分桶表6.sorted by 指定排序（不常用）7.row format delimited　　[fields terminated by char] 　　[collection items terminated by char]　　[map keys terminated by char] 　　[lines terminated char] | serde serde_name [with serdeproperties (property_name=property_value, property_name=property_value, ...)]用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。SerDe 是 Serialize/Deserilize 的简称，目的是用于序列化和反序列化8.stored as 指定存储文件类型9.location指定表在HDFS 上的存储位置。10.like 复制现有的表结构，但是不复制数据建表实例1.直接使用create语句 (if not exists判断是否已存在表，by后面接分隔符)//创建tbl_test1表，列分隔符为 , hive > create table if not exists tbl_test1(key int,value string) row format delimited fields terminated by ',';2.使用子查询的结果集建表 (表名前可加库名(库名.表名))create table 表名 as 子查询hive> create table tbl_test2 as select * from tbl_test1 where id > 1;3.使用子查询的结果集创建临时表create temporary table 表名 as select * from 表名2 limit 200;hive > create temporary table tbl_test3 as select * from tbl_test2 limit 200;4.创建外部表create external table 表名 (字段1 数据类型,字段2 数据类型) row format delimined fields terminated by ',';hive > create external table tbl_test4 (key1 string,key2 string) row format delimined fields terminated by ',';5.使用子查询的结果集创建创建视图create view 视图名 as select * from 表名 limit 200;hive > create view tbl_test5 as select * from tbl_test4 limit 200;6.根据已经存在的表结构创建表（不导入数据）hive > create table if not exists tbl_test6 like tbl_test5;7.创建分区表hive > create table tbl_test7(key1 int, key2 string) partitioned by (partitionkey string) row format delimited fields terminated by '\t';删除表不作判断直接删除hive > drop table tb_name;判断是否存在hive > drop table if exists tb_name;修改表1.重命名表hive > alter table table_name rename to new_table_name2.增加列hive > alter table student add columns (rank string);3.添加分区//添加多个分区partition(分区字段='分区值')以空格分隔 hive > alter table table_name add partition(分区字段='分区值') partition(分区字段='分区值'); 4.删除分区hive > alter table student drop partition(分区字段='分区值');5.替换列hive > alter table table_name replace columns (col_name data_type [comment col_comment], ...)6.更新列hive > alter table table_name change [column] col_old_name col_new_name column_type [comment col_comment] [first|after column_name]

Hive初始化元数据常见报错 总结一些Hive初始化元数据常见的报错，方便查找

Spark抽取MySQL数据到Hive Spark抽取MySQL数据到Hive{lamp/}1.添加Maven依赖<dependencies> <dependency> <groupId>org.apache.spark</groupId> <artifactId>spark-core_2.11</artifactId> <version>2.1.1</version> </dependency> <dependency> <groupId>org.apache.spark</groupId> <artifactId>spark-sql_2.11</artifactId> <version>2.1.1</version> </dependency> <dependency> <groupId>org.apache.spark</groupId> <artifactId>spark-hive_2.11</artifactId> <version>2.1.1</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.38</version> </dependency> </dependencies> <build> <sourceDirectory>src/main/java</sourceDirectory> <plugins> <plugin> <groupId>net.alchim31.maven</groupId> <artifactId>scala-maven-plugin</artifactId> <version>3.3.2</version> <executions> <execution> <id>compile-scala</id> <phase>compile</phase> <goals> <goal>add-source</goal> <goal>compile</goal> </goals> </execution> <execution> <id>test-compile-scala</id> <phase>test-compile</phase> <goals> <goal>add-source</goal> <goal>testCompile</goal> </goals> </execution> </executions> </plugin> </plugins> </build>2.配置Hive-site.xml<property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://master:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false</value> </property> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.jdbc.Driver</value> </property> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>数据库用户名</value> </property> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>数据库密码</value> </property> <property> <name>hive.cli.print.header</name> <value>true</value> </property> <property> <name>hive.cli.print.current.db</name> <value>true</value> </property> <property> <name>hive.metastore.uris</name> <value>thrift://master:9083</value> </property>3.编写scala代码抽取数据<div>import org.apache.spark.sql.{DataFrame, SparkSession} object CreateCustomerTbl { def main(args: Array[String]): Unit = { val sqlSession: SparkSession = SparkSession.builder() .master("local[*]") //指定运行模式，本地调试时使用，打包时应删除，可在集群上指定模式 .appName("createCustpmerTable") .config("hive.metastore.uris", "thrift://localhost:9083") //配置Hive元数据访问 .enableHiveSupport() //开启hive支持 .getOrCreate() val df: DataFrame = sqlSession.read.format("jdbc") .option("url", "jdbc:mysql://localhost:3306/test") .option("driver", "com.mysql.jdbc.Driver") .option("user", "root") .option("password", "123456") .option("dbtable", "customer") .load() df.createOrReplaceTempView("mysql_customer") //在内存中建立临时表 df.show() sqlSession.sql("use ods") //使用Hive的ods库 //建立分区表 sqlSession.sql( """ |create table customer( |custkey string, |name string, |address string, |nationkey string, |phone string, |acctbal string, |mktsegment string, |comment string) |partitioned by (date string) |""".stripMargin) //抽取全量数据到customer表20220503分区中 sqlSession.sql( """ |insert overwrite table customer partition (date = "20220503") select * from mysql_customer |""".stripMargin) sqlSession.close() } }</div>4.打包代码提交至集群运行提交指令：spark-submit --files /usr/local/src/hive/conf/hive-site.xml \ --driver-class-path /usr/local/src/hive/lib/mysql-connector-java-5.1.38.jar \ --master yarn \ --deploy-mode client \ --class CreateCustomerTbl \ /root/Spark-01-1.0-SNAPSHOT.jar 10说明：--files 指定hive-site.xml配置文件位置--driver-class-path 指定mysql驱动位置--master yarn 指定以yarn模式运行--mode-client 以客户端模式运行--class 指定程序运行主类以yarn模式运行时需修改如下配置：修改hadoop yarn-site.xml配置文件，添加以下配置<property> <name>yarn.nodemanager.pmem-check-enabled</name> <value>false</value> </property> <property> <name>yarn.nodemanager.vmem-check-enabled</name> <value>false</value> </property>修改spark-env.sh，添加JAVA_HOME和YARN_CONF_DIRexport JAVA_HOME=jdk所在路径 YARN_CONF_DIR=hadoop配置文件路径（eg:/usr/local/src/hadoop/etc/hadoop）