hive和impala查询数据对比

80 阅读 0 评论 53 点赞

我是靠谱客的博主包容西装，最近开发中收集的这篇文章主要介绍hive和impala查询数据对比，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

首先impala查询数据，更像rdbms一样（mysql）。

--1.impala连接

[root@MASTER01 ~]# impala-shell -islave03;
Starting Impala Shell without Kerberos authentication
Connected to slave03:21000
Server version: impalad version 2.0.0-cdh5 RELEASE (build ecf30af0b4d6e56ea80297df2189367ada6b7da7)
Welcome to the Impala shell. Press TAB twice to see a list of available commands.
Copyright (c) 2012 Cloudera, Inc. All rights reserved.
(Shell build version: Impala Shell v2.0.0-cdh5 (ecf30af) built on Sat Oct 11 13:56:06 PDT 2014)
[slave03:21000] > use tmp;
Query: use tmp
[slave03:21000] > select count(*) from view_0809_02;
Query: select count(*) from view_0809_02
+----------+
| count(*) |
+----------+
| 312923
|
+----------+
Fetched 1 row(s) in 0.52s
[slave03:21000] >

[slave03:21000] > show databases;
Query: show databases
+------------------+
| name
|
+------------------+
| _impala_builtins |
| analyse
|
| default
|
| preparation
|
| result
|
| tmp
|
| trans
|
| unicomidmp
|
| unicomidmptext
|
+------------------+
Fetched 9 row(s) in 0.04s
[slave03:21000] >

--2.hive查询

把你的查询写入到文件中，这里以test为例

[root@MASTER01 ~]# pwd
/root
[root@MASTER01 ~]# more test
select * from tmp.view_0809_02 limit 10;
[root@MASTER01 ~]#

方式一

[root@MASTER01 ~]# hive
2016-07-10 23:13:23,618 WARN
[main] conf.HiveConf (HiveConf.java:initialize(1488)) - DEPRECATED: Configuration property hive.metastore.local no longer has any effect. Make sure to provide a valid value for hive.metastore.uris if you are connecting to a remote metastore.
Logging initialized using configuration in jar:file:/opt/cloudera/parcels/CDH-5.2.0-1.cdh5.2.0.p0.36/jars/hive-common-0.13.1-cdh5.2.0.jar!/hive-log4j.properties
hive> source /root/test;
OK
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
127	12701	0.0	235272.0	0.0	0.0	1995.7121717907662	0.5460680258676013	1	NULL
127	12704	10597.177474518983	15851.0	0.0	0.0	2214.811959427583	0.593517086204472	1	甯..
127	12701	9000.215321291445	15180.0	0.0	0.0	1635.4721847739888	0.5304609512624779	1	甯..
127	12701	9127.899911139766	12088.0	0.0	0.0	1315.3312952660858	0.4074925744945866	1	甯..
127	12709	7248.966716871597	12211.0	0.0	0.0	1917.6872222706281	0.47774480712166173	1	甯..
127	12701	7173.393871860499	13058.0	0.0	0.0	1618.2031788141114	0.4941640920013732	1	甯..
127	12702	6611.041099384324	14520.0	0.0	0.0	2039.5076826097234	0.5196894453381828	1	甯..
127	12701	7141.804228551254	5984.0	0.0	0.0	1666.4142210944397	0.46508058326937834	1	甯..
127	12701	7299.380089996009	3047.0	0.0	0.0	1652.1592062759576	0.5077308817384036	1	甯..
127	12703	6979.598212361227	5697.0	0.0	0.0	1696.5090044329515	0.4653299916457811	1	甯..
Time taken: 1.225 seconds, Fetched: 10 row(s)
hive>

方式二

[root@MASTER01 ~]# hive -f test
2016-07-10 23:14:20,138 WARN
[main] conf.HiveConf (HiveConf.java:initialize(1488)) - DEPRECATED: Configuration property hive.metastore.local no longer has any effect. Make sure to provide a valid value for hive.metastore.uris if you are connecting to a remote metastore.
Logging initialized using configuration in jar:file:/opt/cloudera/parcels/CDH-5.2.0-1.cdh5.2.0.p0.36/jars/hive-common-0.13.1-cdh5.2.0.jar!/hive-log4j.properties
OK
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
127	12701	0.0	235272.0	0.0	0.0	1995.7121717907662	0.5460680258676013	1	NULL
127	12704	10597.177474518983	15851.0	0.0	0.0	2214.811959427583	0.593517086204472	1	市区
127	12701	9000.215321291445	15180.0	0.0	0.0	1635.4721847739888	0.5304609512624779	1	市区
127	12701	9127.899911139766	12088.0	0.0	0.0	1315.3312952660858	0.4074925744945866	1	市区
127	12709	7248.966716871597	12211.0	0.0	0.0	1917.6872222706281	0.47774480712166173	1	市区
127	12701	7173.393871860499	13058.0	0.0	0.0	1618.2031788141114	0.4941640920013732	1	市区
127	12702	6611.041099384324	14520.0	0.0	0.0	2039.5076826097234	0.5196894453381828	1	市区
127	12701	7141.804228551254	5984.0	0.0	0.0	1666.4142210944397	0.46508058326937834	1	市区
127	12701	7299.380089996009	3047.0	0.0	0.0	1652.1592062759576	0.5077308817384036	1	市区
127	12703	6979.598212361227	5697.0	0.0	0.0	1696.5090044329515	0.4653299916457811	1	市区
Time taken: 1.423 seconds, Fetched: 10 row(s)
Jul 10, 2016 11:14:23 PM WARNING: parquet.hadoop.ParquetRecordReader: Can not initialize counter due to context is not a instance of TaskInputOutputContext, but is org.apache.hadoop.mapreduce.task.TaskAttemptContextImpl
Jul 10, 2016 11:14:23 PM INFO: parquet.hadoop.InternalParquetRecordReader: RecordReader initialized will read a total of 23383 records.
Jul 10, 2016 11:14:23 PM INFO: parquet.hadoop.InternalParquetRecordReader: at row 0. reading next block
Jul 10, 2016 11:14:23 PM INFO: parquet.hadoop.InternalParquetRecordReader: block read in memory in 29 ms. row count = 23383
[root@MASTER01 ~]#

方式三，直接用sql

[root@MASTER01 ~]# hive -e 'select * from tmp.view_0809_02 limit 10'
2016-07-10 23:31:35,744 WARN
[main] conf.HiveConf (HiveConf.java:initialize(1488)) - DEPRECATED: Configuration property hive.metastore.local no longer has any effect. Make sure to provide a valid value for hive.metastore.uris if you are connecting to a remote metastore.
Logging initialized using configuration in jar:file:/opt/cloudera/parcels/CDH-5.2.0-1.cdh5.2.0.p0.36/jars/hive-common-0.13.1-cdh5.2.0.jar!/hive-log4j.properties
OK
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
127	12701	0.0	235272.0	0.0	0.0	1995.7121717907662	0.5460680258676013	1	NULL
127	12704	10597.177474518983	15851.0	0.0	0.0	2214.811959427583	0.593517086204472	1	甯..
127	12701	9000.215321291445	15180.0	0.0	0.0	1635.4721847739888	0.5304609512624779	1	甯..
127	12701	9127.899911139766	12088.0	0.0	0.0	1315.3312952660858	0.4074925744945866	1	甯..
127	12709	7248.966716871597	12211.0	0.0	0.0	1917.6872222706281	0.47774480712166173	1	甯..
127	12701	7173.393871860499	13058.0	0.0	0.0	1618.2031788141114	0.4941640920013732	1	甯..
127	12702	6611.041099384324	14520.0	0.0	0.0	2039.5076826097234	0.5196894453381828	1	甯..
127	12701	7141.804228551254	5984.0	0.0	0.0	1666.4142210944397	0.46508058326937834	1	甯..
127	12701	7299.380089996009	3047.0	0.0	0.0	1652.1592062759576	0.5077308817384036	1	甯..
127	12703	6979.598212361227	5697.0	0.0	0.0	1696.5090044329515	0.4653299916457811	1	甯..
Time taken: 1.491 seconds, Fetched: 10 row(s)
Jul 10, 2016 11:31:39 PM WARNING: parquet.hadoop.ParquetRecordReader: Can not initialize counter due to context is not a instance of TaskInputOutputContext, but is org.apache.hadoop.mapreduce.task.TaskAttemptContextImpl
Jul 10, 2016 11:31:39 PM INFO: parquet.hadoop.InternalParquetRecordReader: RecordReader initialized will read a total of 23383 records.
Jul 10, 2016 11:31:39 PM INFO: parquet.hadoop.InternalParquetRecordReader: at row 0. reading next block
Jul 10, 2016 11:31:39 PM INFO: parquet.hadoop.InternalParquetRecordReader: block read in memory in 53 ms. row count = 23383
[root@MASTER01 ~]#

其他参数尝试，参加官方文档

[root@MASTER01 ~]# hive -help
2016-07-10 23:34:54,959 WARN
[main] conf.HiveConf (HiveConf.java:initialize(1488)) - DEPRECATED: Configuration property hive.metastore.local no longer has any effect. Make sure to provide a valid value for hive.metastore.uris if you are connecting to a remote metastore.
usage: hive
-d,--define <key=value>
Variable subsitution to apply to hive
commands. e.g. -d A=B or --define A=B
--database <databasename>
Specify the database to use
-e <quoted-query-string>
SQL from command line
-f <filename>
SQL from files
-H,--help
Print help information
-h <hostname>
connecting to Hive Server on remote host
--hiveconf <property=value>
Use value for given property
--hivevar <key=value>
Variable subsitution to apply to hive
commands. e.g. --hivevar A=B
-i <filename>
Initialization SQL file
-p <port>
connecting to Hive Server on port number
-S,--silent
Silent mode in interactive shell
-v,--verbose
Verbose mode (echo executed SQL to the
console)