3.1. presto客户端使用

presto提供了客户端查询进行sql查询。而且在客户端中可以自己指定catalog和schema，非常方便快速的定位到我们需要查询的表。

3.1.1. 帮助文档

root@docker:/usr/local/presto-server-0.217# ./presto --help
NAME
        presto - Presto interactive console

SYNOPSIS
        presto [--access-token <access token>] [--catalog <catalog>]
                [--client-info <client-info>]
                [--client-request-timeout <client request timeout>]
                [--client-tags <client tags>] [--debug] [--execute <execute>]
                [(-f <file> | --file <file>)] [(-h | --help)]
                [--http-proxy <http-proxy>] [--ignore-errors]
                [--keystore-password <keystore password>]
                [--keystore-path <keystore path>]
                [--krb5-config-path <krb5 config path>]
                [--krb5-credential-cache-path <krb5 credential cache path>]
                [--krb5-disable-remote-service-hostname-canonicalization]
                [--krb5-keytab-path <krb5 keytab path>]
                [--krb5-principal <krb5 principal>]
                [--krb5-remote-service-name <krb5 remote service name>]
                [--log-levels-file <log levels file>] [--output-format <output-format>]
                [--password] [--resource-estimate <resource-estimate>...]
                [--schema <schema>] [--server <server>] [--session <session>...]
                [--socks-proxy <socks-proxy>] [--source <source>]
                [--truststore-password <truststore password>]
                [--truststore-path <truststore path>] [--user <user>] [--version]

OPTIONS
        --access-token <access token>
            Access token

        --catalog <catalog>
            Default catalog

        --client-info <client-info>
            Extra information about client making query

        --client-request-timeout <client request timeout>
            Client request timeout (default: 2m)

        --client-tags <client tags>
            Client tags

        --debug
            Enable debug information

        --execute <execute>
            Execute specified statements and exit

        -f <file>, --file <file>
            Execute statements from file and exit

        -h, --help
            Display help information

        --http-proxy <http-proxy>
            HTTP proxy to use for server connections

        --ignore-errors
            Continue processing in batch mode when an error occurs (default is
            to exit immediately)

        --keystore-password <keystore password>
            Keystore password

        --keystore-path <keystore path>
            Keystore path

        --krb5-config-path <krb5 config path>
            Kerberos config file path (default: /etc/krb5.conf)

        --krb5-credential-cache-path <krb5 credential cache path>
            Kerberos credential cache path

        --krb5-disable-remote-service-hostname-canonicalization
            Disable service hostname canonicalization using the DNS reverse
            lookup

        --krb5-keytab-path <krb5 keytab path>
            Kerberos key table path (default: /etc/krb5.keytab)

        --krb5-principal <krb5 principal>
            Kerberos principal to be used

        --krb5-remote-service-name <krb5 remote service name>
            Remote peer's kerberos service name

        --log-levels-file <log levels file>
            Configure log levels for debugging using this file

        --output-format <output-format>
            Output format for batch mode [ALIGNED, VERTICAL, CSV, TSV,
            CSV_HEADER, TSV_HEADER, NULL] (default: CSV)

        --password
            Prompt for password

        --resource-estimate <resource-estimate>
            Resource estimate (property can be used multiple times; format is
            key=value)

        --schema <schema>
            Default schema

        --server <server>
            Presto server location (default: localhost:8080)

        --session <session>
            Session property (property can be used multiple times; format is
            key=value; use 'SHOW SESSION' to see available properties)

        --socks-proxy <socks-proxy>
            SOCKS proxy to use for server connections

        --source <source>
            Name of source making query

        --truststore-password <truststore password>
            Truststore password

        --truststore-path <truststore path>
            Truststore path

        --user <user>
            Username

        --version
            Display version information and exit

3.1.2. 使用方式

./presto --server localhost:9001 --catalog hive --schema default

3.1.3. 制作脚本

echo "./presto --server localhost:9001 --catalog hive --schema default" > presto-cli-hive
chmod +x presto-cli-hive
ln -s /usr/local/presto-server-0.217/presto-cli-hive /usr/local/bin/

3.1.4. 执行sql语句

root@docker:/usr/local/presto-server-0.217# ./presto --server localhost:9001 --schema default --catalog hive --execute "show catalogs";
"elasticsearch"
"hive"
"kafka"
"postgres"
"system"
root@docker:/usr/local/presto-server-0.217# ./presto --server localhost:9001 --schema default --catalog hive --execute "show tables;";
"t_ods_industry_flow"
root@docker:/usr/local/presto-server-0.217# ./presto --server localhost:9001 --schema default --catalog hive --execute "select count(1) from t_ods_industry_flow";
"11904"

3.1.5. 执行sql文件

echo "select count(1) from t_ods_industry_flow;" > hive_default_flow.sql
root@docker:/usr/local/presto-server-0.217# ./presto --server localhost:9001 --schema default --catalog hive --file hive_default_flow.sql
"11904"

3.1.6. sql结果导出

root@docker:/usr/local/presto-server-0.217# ./presto --server localhost:9001 --schema default --catalog hive --execute "select count(1) from t_ods_industry_flow" --output-format CSV > result.csv
root@docker:/usr/local/presto-server-0.217# cat result.csv
"11904"

root@docker:/usr/local/presto-server-0.217# ./presto --server localhost:9001 --schema default --catalog hive --file hive_default_flow.sql --output-format CSV > result2.csv
root@docker:/usr/local/presto-server-0.217# cat result2.csv
"11904"