- 1，方式1: `-e 'sql1' 'sql2' 'sql3' `
- hive -e $sql_array
- 2，方式2: `!sql str1; str2; str3`
- 3，beeline 显示方式和 hive一致
beeline 默认不能支持hive的 -e ‘sql1;sql2;sql3’ 这种语句：https://issues.apache.org/jira/browse/HIVE-9877
1，方式1: -e 'sql1' 'sql2' 'sql3'[root@hw10 ~]# beeline -e "show databases" "use test" "show tables" "select * from score limit 2" 'create table t1 like score stored as textfile' SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/export/huawei/client/Hive/Beeline/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/export/huawei/client/Hive/Beeline/lib/slf4j-log4j12-1.7.5.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] log4j:WARN No appenders could be found for logger (org.apache.zookeeper.client.FourLetterWordMain). log4j:WARN Please initialize the log4j system properly. log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info. +----------------+ | database_name | +----------------+ | default | | test | | test_tmp | | test | +----------------+ +-----------+ | tab_name | +-----------+ | score | | t1 | +-----------+ +-----------+--------------+ | score.id | score.score | +-----------+--------------+ | a01 | 100 | | a02 | 80 | +-----------+--------------+hive -e $sql_array
[root@hw10 ~]# IFS=';' arr=(" show databases; use test; show tables; select * from tab1") ; for i in ${arr[*]}; do echo $i; done
show databases
use test
show tables
select * from tab1
[root@hw10 ~]# IFS=';' arr=("show databases; use test; show tables; select * from tab1") ; hive -e $arr
2，方式2: !sql str1; str2; str3
SQLLine Cli 命令支持:
- https://www.docs4dev.com/docs/zh/apache-hive/3.1.1/reference/HiveServer2_Clients.html
- http://sqlline.sourceforge.net/#sect_command_sql
0: jdbc:hive2://10.222.0.18:21066/> help
!addlocaldriverjar Add driver jar file in the beeline client side.
!addlocaldrivername Add driver name that needs to be supported in the beeline
!tables List all the tables in the database
!columns List all the columns for the specified table
!describe Describe a table
!dropall Drop all tables in the current database
!dbinfo Give metadata information about the database
!list List the current connections
!metadata Obtain metadata information
!nullemptystring Set to true to get historic behavior of printing null as
empty string. Default is false.
!outputformat Set the output format for displaying results
(table,vertical,csv2,dsv,tsv2,xmlattrs,xmlelements, and
deprecated formats(csv, tsv))
!set Set a beeline variable
!delimiter Sets the query delimiter, defaults to ;
!run Run a script from the specified file
!sql Execute a SQL command
!sh Execute a shell command
0: jdbc:hive2://10.222.0.18:21066/> !sql show databases; use test; show tables; select * from score limit 2;
+----------------+
| database_name |
+----------------+
| default |
| test |
| test_tmp |
| test |
+----------------+
+-----------+
| tab_name |
+-----------+
| score |
| t1 |
+-----------+
+-----------+--------------+
| score.id | score.score |
+-----------+--------------+
| a01 | 100 |
| a02 | 80 |
+-----------+--------------+
[root@hw10 ~]# a="a01"
[root@hw10 ~]# cmd='!sql'
[root@hw10 ~]# echo "$cmd show databases ; use test ; show tables ; select * from score where id >'${a}' limit 2"
!sql show databases ; use test ; show tables ; select * from score where id >'a01' limit 2
[root@hw10 ~]#
[root@hw10 ~]# echo "$cmd show databases ; use test ; show tables ; select * from score where id >'${a}' limit 2" |beeline
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/export/huawei/client/Hive/Beeline/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/export/huawei/client/Hive/Beeline/lib/slf4j-log4j12-1.7.5.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]
log4j:WARN No appenders could be found for logger (org.apache.zookeeper.client.FourLetterWordMain).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
0: jdbc:hive2://10.222.0.17:21066/> !sql show databases ; use test ; show tables ; select * from score where id >'a01' limit 2
0: jdbc:hive2://10.222.0.17:21066/> +----------------+
| database_name |
+----------------+
| default |
| test |
| test_tmp |
| test |
+----------------+
+-----------+
| tab_name |
+-----------+
| score |
| t1 |
+-----------+
+-----------+--------------+
| score.id | score.score |
+-----------+--------------+
| a02 | 80 |
| a03 | 92 |
+-----------+--------------+
0: jdbc:hive2://10.222.0.17:21066/> [root@hw10 ~]#
3，beeline 显示方式和 hive一致
[root@hw10 ~]# beeline --help Usage: java org.apache.hive.cli.beeline.BeeLine -dthe driver class to use -i script file for initialization -e query that should be executed -f script file that should be executed -w (or) --password-file the password file to read password from --hiveconf property=value Use value for given property --hivevar name=value hive variable name and value This is Hive specific settings in which variables can be set at session level and referenced in Hive commands or queries. --property-file= the file to read connection properties (url, driver, user, password) from --color=[true/false] control whether color is used for display --showHeader=[true/false] show column names in query results --escapeCRLF=[true/false] show carriage return and line feeds in query results as escaped r and n --headerInterval=ROWS; the interval between which heades are displayed --fastConnect=[true/false] skip building table/column list for tab-completion --autoCommit=[true/false] enable/disable automatic transaction commit --verbose=[true/false] show verbose error messages and debug info --showWarnings=[true/false] display connection warnings --showDbInprompt=[true/false] display the current database name in the prompt --showNestedErrs=[true/false] display nested errors --numberFormat=[pattern] format numbers using DecimalFormat pattern --force=[true/false] continue running script even after errors --maxWidth=MAXWIDTH the maximum width of the terminal --maxColumnWidth=MAXCOLWIDTH the maximum width to use when displaying columns --silent=[true/false] be more silent --autosave=[true/false] automatically save preferences --outputformat=[table/vertical/csv2/tsv2/dsv/csv/tsv] format mode for result display Note that csv, and tsv are deprecated - use csv2, tsv2 instead [root@hw10 ~]# beeline --showWarnings=false --verbose=false --silent=true --outputformat=tsv2 --hiveconf hive.cli.print.header=true --hiveconf hive.resultset.use.unique.column.names=false SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/export/huawei/client/Hive/Beeline/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/export/huawei/client/Hive/Beeline/lib/slf4j-log4j12-1.7.5.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] log4j:WARN No appenders could be found for logger (org.apache.zookeeper.client.FourLetterWordMain). log4j:WARN Please initialize the log4j system properly. log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info. 0: jdbc:hive2://10.222.0.17:21066/> show databases; database_name default test test_tmp test 0: jdbc:hive2://10.222.0.17:21066/> use test; 0: jdbc:hive2://10.222.0.17:21066/> show tables; tab_name score t1 0: jdbc:hive2://10.222.0.17:21066/> select * from score; id score a01 100 a02 80 a03 92 a03 99
