beeline 支持多条sql

悠悠 2022-09-14 01:48 359阅读 0赞

### 文章目录 ###

*  1，方式1: \`-e 'sql1' 'sql2' 'sql3' \`
 *   *  \`beeline -e $sql\_array\`
 *  2，方式2: \`!sql str1; str2; str3\`
 *   *  a, \`beeline -e "!sql str1; str2;str3"\`
     *  b, \`beeline -e "!sql set xx=yy; str1;str2;str3"\`
     *  c, python调用shell: 执行hive查询
 *  3，beeline 显示方式和 hive一致

beeline 3.1.0及之前的版本 默认不能支持hive的 -e ‘sql1;sql2;sql3’ 这种语句：[https://issues.apache.org/jira/browse/HIVE-9877][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           |
    +-----------+--------------+

## `beeline -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") ; beeline -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][https_www.docs4dev.com_docs_zh_apache-hive_3.1.1_reference_HiveServer2_Clients.html]
 *  [http://sqlline.sourceforge.net/\#sect\_command\_sql][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 ~]#

## a, `beeline -e "!sql str1; str2;str3"` ##

######## 1，脚本环境运行，无报错
    [root@hw10 ~]# cat /tmp/a.sh 
    beeline -e "!sql show databases; use test; show tables; select * from score";  
    exit
    
    su hive <<-EOF beeline -e "!sql show databases; use test; show tables; select * from score"; EOF
    
    [root@hw10 ~]# sh /tmp/a.sh
    +----------------+
    | database_name  |
    +----------------+
    | default        |
    | kuai           |
    | kuai_tmp       |
    | test           |
    +----------------+
    +-----------+
    | tab_name  |
    +-----------+
    | score     |
    | t1        |
    +-----------+
    +-----------+--------------+
    | score.id  | score.score  |
    +-----------+--------------+
    | a01       | 100          |
    | a02       | 80           |
    | a03       | 92           |
    | a03       | 99           |
    +-----------+--------------+
    
    
    ######## 2，直接shell交互式环境运行，解决报错
    [root@hw10 ~]# beeline -e "!sql show databases; use test; show tables; select * from score";
    -bash: !sql: event not found
    
    [root@hw10 ~]# cmd='!sql'
    [root@hw10 ~]# beeline -e "$cmd show databases; use test; show tables; select * from score";
    +----------------+
    | database_name  |
    +----------------+
    | default        |
    | kuai           |
    | kuai_tmp       |
    | test           |
    +----------------+
    +-----------+
    | tab_name  |
    +-----------+
    | score     |
    | t1        |
    +-----------+
    +-----------+--------------+
    | score.id  | score.score  |
    +-----------+--------------+
    | a01       | 100          |
    | a02       | 80           |
    | a03       | 92           |
    | a03       | 99           |
    +-----------+--------------+

## b, `beeline -e "!sql set xx=yy; str1;str2;str3"` ##

[root@hw10 ~]# cmd='!sql'
    [root@hw10 ~]# beeline -e "$cmd set hive.cli.print.header=true;set hive.resultset.use.unique.column.names=false;select * from test.score limit 2"
    +------+--------+
    |  id  | score  |
    +------+--------+
    | a01  | 100    |
    | a02  | 80     |
    +------+--------+
    
    [root@hw10 ~]# beeline -e "$cmd select * from test.score limit 2"
    +-----------+--------------+
    | score.id  | score.score  |
    +-----------+--------------+
    | a01       | 100          |
    | a02       | 80           |
    +-----------+--------------+

## c, python调用shell: 执行hive查询 ##

>>> import os,subprocess
    >>> os.environ['cmd'] = '!sql'
    >>> subprocess.check_call('echo $cmd', shell=True, env=dict(os.environ) )
    !sql
    0
    
    >>> sql=""" beeline -e "$cmd set hive.cli.print.header=True;set hive.resultset.use.unique.column.names=false;select * from test.test_out1 where dt='{dt}' limit 2" """.format(dt='2021-09-02')   
    >>> print sql
     beeline -e "$cmd set hive.cli.print.header=True;set hive.resultset.use.unique.column.names=false;select * from test.test_out1 where dt='2021-09-02' limit 2"  
    
    
    >>> subprocess.check_call(sql, shell=True, env=dict(os.environ) )
    +-------+--------------+----------------------+-------------+
    |  id   | probability  |      score           |     dt      |
    +-------+--------------+----------------------+-------------+
    | 1.0   | 1            | 0.9272812802360115   | 2021-09-02  |
    | 2.0   | 1            | 0.5673338808676501   | 2021-09-02  |
    +-------+--------------+----------------------+-------------+
    
    >>> os.system(sql)
    +-------+--------------+----------------------+-------------+
    |  id   | probability  |      score           |     dt      |
    +-------+--------------+----------------------+-------------+
    | 1.0   | 1            | 0.9272812802360115   | 2021-09-02  |
    | 2.0   | 1            | 0.5673338808676501   | 2021-09-02  |
    +-------+--------------+----------------------+-------------+

# 3，beeline 显示方式和 hive一致 #

[root@hw10 ~]# beeline --help
    Usage: java org.apache.hive.cli.beeline.BeeLine 
       -d <driver class>               the driver class to use
       -i <init file>                  script file for initialization
       -e <query>                      query that should be executed
       -f <exec file>                  script file that should be executed
       -w (or) --password-file <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=<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

[https_issues.apache.org_jira_browse_HIVE-9877]: https://issues.apache.org/jira/browse/HIVE-9877
[https_www.docs4dev.com_docs_zh_apache-hive_3.1.1_reference_HiveServer2_Clients.html]: https://www.docs4dev.com/docs/zh/apache-hive/3.1.1/reference/HiveServer2_Clients.html
[http_sqlline.sourceforge.net_sect_command_sql]: http://sqlline.sourceforge.net/#sect_command_sql