Presto技术调研

tar -zxvf presto-server-0.181.tar.gz
cd presto-server-0.181
mkdir etc
cd etc
mkdir catalog
#这个是数据目录，存放日志以及一些配置文件
mkdir -p /home/qun/data/presto  

[qun@slave2 presto-server-0.181]$ tree -I *.jar
.
|-- bin
|   |-- launcher
|   |-- launcher.properties
|   |-- launcher.py
|   `-- procname
|       `-- Linux-x86_64
|           `-- libprocname.so
|-- etc
|   |-- catalog
|   |   `-- mysql.properties
|   |-- config.properties
|   |-- jvm.config
|   `-- node.properties
|-- lib
|-- NOTICE
|-- plugin
|   |-- accumulo
|   |-- atop
|   |-- blackhole
|   |-- cassandra
|   |-- example-http
|   |-- hive-hadoop2
|   |-- jmx
|   |-- kafka
|   |-- localfile
|   |-- memory
|   |-- ml
|   |-- mongodb
|   |-- mysql
|   |-- postgresql
|   |-- presto-thrift
|   |-- raptor
|   |-- redis
|   |-- resource-group-managers
|   |-- sqlserver
|   |-- teradata-functions
|   `-- tpch
`-- README.txt

./bin/launcher start
./bin/launcher start

[qun@slave2 log]$ ll /home/qun/data/presto/var/log/
总用量 8388
-rw-rw-r--  1 qun qun 8306295 7月  30 22:40 http-request.log
-rw-r--r--. 1 qun qun    1512 7月  29 21:24 launcher.log
-rw-rw-r--  1 qun qun   26582 7月  30 17:37 server.log

yum install krb5-libs.x86_64 krb5-workstation.x86_64 krb5

[root@slave1 qun]# vi /etc/krb5.conf 
[logging]
 default = FILE:/var/log/krb5libs.log
 kdc = FILE:/var/log/krb5kdc.log
 admin_server = FILE:/var/log/kadmind.log

[libdefaults]
 default_realm = XIAOMI.PRESTO
 dns_lookup_realm = false
 dns_lookup_kdc = false
 ticket_lifetime = 24h
 renew_lifetime = 7d
 forwardable = true

[realms]
 XIAOMI.PRESTO = {
  kdc = xiaobin
  admin_server = xiaobin
 }

[domain_realm]
 .xiaomi.presto = XIAOMI.PRESTO
 xiaomi.presto = XIAOMI.PRESTO

kadmin -p admin/admin -q "addprinc -randkey qun@XIAOMI.PRESTO"
kadmin -p admin/admin -q "addprinc -randkey qun/slave1@XIAOMI.PRESTO"
kadmin -p admin/admin -q "ktadd -k /etc/qun.keytab qun@XIAOMI.PRESTO"
kadmin -p admin/admin -q "ktadd -k /etc/qun.keytab qun/slave1@XIAOMI.PRESTO"

[root@slave1 qun]# keytool -genkeypair -alias qun -keyalg RSA -keystore qunkeystore.jks
输入密钥库口令:  
再次输入新口令: 
您的名字与姓氏是什么?
  [Unknown]:  slave1
您的组织单位名称是什么?
  [Unknown]:  
您的组织名称是什么?
  [Unknown]:  
您所在的城市或区域名称是什么?
  [Unknown]:  
您所在的省/市/自治区名称是什么?
  [Unknown]:  
该单位的双字母国家/地区代码是什么?
  [Unknown]:  
CN=slave1, OU=Unknown, O=Unknown, L=Unknown, ST=Unknown, C=Unknown是否正确?
  [否]:  y

输入 <qun> 的密钥口令
    (如果和密钥库口令相同, 按回车):  
再次输入新口令:

wget http://download.oracle.com/otn-pub/java/jce/8/jce_policy-8.zip
将解压的jar放到如下目录中，主要是local_policy.jar和US_export_policy.jar
$JAVA_HOME/jre/lib/security/

coordinator=true
node-scheduler.include-coordinator=false
http-server.http.port=8999
query.max-memory=2GB
query.max-memory-per-node=1GB
discovery-server.enabled=true
discovery.uri=http://slave1:8999


http-server.authentication.type=KERBEROS

http.server.authentication.krb5.service-name=qun
http.server.authentication.krb5.keytab=/home/qun/presto-server-0.181/qun.keytab
http.authentication.krb5.config=/home/qun/presto-server-0.181/krb5.conf

http-server.https.enabled=true
http-server.https.port=7778

http-server.https.keystore.path=/home/qun/presto-server-0.181/qunkeystore.jks
http-server.https.keystore.key=keystorepd

[root@hadoop102~] vim /opt/module/presto/etc/catalog/hive.properties

hive.metastore.authentication.type=KERBEROS
hive.metastore.service.principal=hive/hadoop102@EXAMPLE.COM
hive.metastore.client.principal=presto@EXAMPLE.COM
hive.metastore.client.keytab=/etc/security/keytab/presto.keytab

hive.hdfs.authentication.type=KERBEROS
hive.hdfs.impersonation.enabled=true
hive.hdfs.presto.principal=presto@EXAMPLE.COM
hive.hdfs.presto.keytab=/etc/security/keytab/presto.keytab
hive.config.resources=/opt/module/hadoop-3.1.3/etc/hadoop/core-site.xml,/opt/module/hadoop-3.1.3/etc/hadoop/hdfs-site.xml

-server
-Xmx2G
-XX:+UseG1GC
-XX:G1HeapRegionSize=32M
-XX:+UseGCOverheadLimit
-XX:+ExplicitGCInvokesConcurrent
-XX:+HeapDumpOnOutOfMemoryError
-Dsun.security.krb5.debug=true
-Dlog.enable-console=true

./presto-cli  \
  --server https://slave1:7778 \
  --enable-authentication \
  --krb5-config-path /etc/krb5.conf \
  --krb5-principal qun@XIAOMI.PRESTO \
  --krb5-keytab-path /home/qun/qun.keytab \
  --krb5-remote-service-name qun \
  --keystore-path /home/qun/qunkeystore.jks \
  --keystore-password keystorepd 

presto> select count(*) from mysql.test.user;
 _col0 
-------
     1 
(1 row)

Query 20170730_021003_00003_bpf6r, FINISHED, 1 node
Splits: 18 total, 18 done (100.00%)
0:00 [1 rows, 0B] [3 rows/s, 0B/s]

kadmin -p admin/admin -q "addprinc -randkey test1@XIAOMI.PRESTO"
kadmin -p admin/admin -q "ktadd -k /etc/test1.keytab test1@XIAOMI.PRESTO"

[test@slave3 ~]$ ./presto-cli \
>   --server https://slave1:7778 \
>   --enable-authentication \
>   --krb5-config-path /etc/krb5.conf \
>   --krb5-principal test1@XIAOMI.PRESTO \
>   --krb5-keytab-path test1.keytab \
>   --krb5-remote-service-name qun \
>   --keystore-path qunkeystore.jks \
>   --keystore-password keystorepd
presto> show catalogs;
 Catalog 
---------
 mysql   
 system  
(2 rows)

Query 20170801_134406_00005_wa4q4, FINISHED, 1 node
Splits: 1 total, 1 done (100.00%)
0:00 [0 rows, 0B] [0 rows/s, 0B/s]

presto> select count(*) from mysql.test.user;
 _col0 
-------
     1 
(1 row)

Query 20170801_134419_00006_wa4q4, FINISHED, 1 node
Splits: 18 total, 18 done (100.00%)
0:00 [1 rows, 0B] [3 rows/s, 0B/s]

[GOOD]: SELECT time, user, host FROM tbl where acct_day=20171101

[BAD]:  SELECT * FROM tbl where visit_time=20171101

[GOOD]: SELECT GROUP BY uid, gender

[BAD]:  SELECT GROUP BY gender, uid

[GOOD]: SELECT * FROM tbl ORDER BY time LIMIT 100

[BAD]:  SELECT * FROM tbl ORDER BY time

[GOOD] SELECT ... FROM large_table l join small_table s on l.id = s.id
[BAD] SELECT ... FROM small_table s join large_table l on l.id = s.id

/*MySQL的写法*/
SELECT t FROM a WHERE t > '2017-01-01 00:00:00'; 

/*Presto中的写法*/
SELECT t FROM a WHERE t > timestamp '2017-01-01 00:00:00';

presto> show catalogs;
Error running command: javax.net.ssl.SSLPeerUnverifiedException: Hostname 192.168.1.116 not verified:
 certificate: sha256/i+KNkzrrH/NHzUruc9R+f0a/P8Ql/OhOKh9n3JtL1qg=
 DN: CN=Unknown, OU=Unknown, O=Unknown, L=Unknown, ST=Unknown, C=Unknown
 subjectAltNames: []

2. Authentication failed for token
```shell
com.facebook.presto.server.security.SpnegoFilter  Authentication failed for token
  Encryption type AES256 CTS mode with HMAC SHA1-96 is not supported/enabled)

解决办法：下载JCE
  http://www.oracle.com/technetwork/java/javase/downloads/jce8-download-2133166.html
  解压后替换$JAVA_HOME/jre/lib/security目录下的local_policy.jar，US_export_policy.jar

HDFS 小文件问题在大数据领域是个常见的问题。数仓 Hive 表有些表的文件有几千个，查询特别慢。Presto 下面这两个参数限制了 Presto 每个节点每个 Task 可执行的最大 Split 数目：
node-scheduler.max-splits-per-node=100
node-scheduler.max-pending-splits-per-task=10

--简单的说，正常的优化器应该使用 grouping sets 去将多个 group by 整合到一起来提升性能：
SELECT a1, a2,..., an, F1(b1), F2(b2), F3(b3), ...., Fm(bm), F1(distinct c1), ...., Fm(distinct cm) FROM Table GROUP BY a1, a2, ..., an

转换为

SELECT a1, a2,..., an, arbitrary(if(group = 0, f1)),...., arbitrary(if(group = 0, fm)), F(if(group = 1, c1)), ...., F(if(group = m, cm)) FROM
    SELECT a1, a2,..., an, F1(b1) as f1, F2(b2) as f2,...., Fm(bm) as fm, c1,..., cm group FROM
      SELECT a1, a2,..., an, b1, b2, ... ,bn, c1,..., cm FROM Table GROUP BY GROUPING SETS ((a1, a2,..., an, b1, b2, ... ,bn), (a1, a2,..., an, c1), ..., ((a1, a2,..., an, cm)))
    GROUP BY a1, a2,..., an, c1,..., cm group
GROUP BY a1, a2,..., an