Phoenix对接Superset+Presto

最新推荐文章于 2024-09-12 18:00:00 发布

原创最新推荐文章于 2024-09-12 18:00:00 发布 · 553 阅读

2 ·

CC 4.0 BY-SA版权

实时数仓专栏收录该内容

1 篇文章

订阅专栏

本文介绍了如何将Phoenix与Superset和Presto整合，包括安装prestosql、配置Presto连接Phoenix、在Superset中添加Presto数据源以及解决时区和数据类型转换问题。

Phoenix对接Superset+Presto

安装prestosql

参考：https://prestosql.io/docs/current/installation/deployment.html

Presto配置Phoenix连接

新建文件/presto-server-331/etc/catalog/phoenix.propertie
添加配置：

connector.name=phoenix
case-insensitive-name-matching=true
phoenix.connection-url=jdbc:phoenix:xxx,xxx,xxxx:2181:/hbase
phoenix.config.resources=/home/presto/hbase-site.xml,/home/presto/core-site.xml,/home/presto/hdfs-site.xml,/home/presto/ssl-client.xml`

重启Presto服务

bin/launcher restart

命令参考:

bin/launcher --help
Usage: launcher [options] command

Commands: run, start, stop, restart, kill, status

Options:
-h, --help               show this help message and exit
-v, --verbose            Run verbosely
--etc-dir=DIR            Defaults to INSTALL_PATH/etc
--launcher-config=FILE   Defaults to INSTALL_PATH/bin/launcher.properties
--node-config=FILE       Defaults to ETC_DIR/node.properties
--jvm-config=FILE        Defaults to ETC_DIR/jvm.config
--config=FILE            Defaults to ETC_DIR/config.properties
--log-levels-file=FILE   Defaults to ETC_DIR/log.properties
--data-dir=DIR           Defaults to INSTALL_PATH
--pid-file=FILE          Defaults to DATA_DIR/var/run/launcher.pid
--launcher-log-file=FILE Defaults to DATA_DIR/var/log/launcher.log (only in
                         daemon mode)
--server-log-file=FILE   Defaults to DATA_DIR/var/log/server.log (only in
                         daemon mode)
-D NAME=VALUE            Set a Java system property

查看Presto中Phoenix表

./presto-server-331/presto --server localhost:8080 --catalog phoenix

presto> show schemas;
       Schema       
--------------------
 default            
 information_schema 
 mytest             
 system             
(4 rows)

Query 20200328_020428_00002_5z3ak, FINISHED, 2 nodes
Splits: 36 total, 36 done (100.00%)
0:00 [4 rows, 57B] [11 rows/s, 164B/s]

presto> use default;
USE
presto:default> show tables;
               Table                
------------------------------------
省略.....
...
...
...
(24 rows)

Query 20200328_020518_00006_5z3ak, FINISHED, 2 nodes
Splits: 36 total, 36 done (100.00%)
0:00 [24 rows, 824B] [115 rows/s, 3.87KB/s]


presto:default> exit

Supserset中添加Presto

QQ截图20200328101017.png

QQ截图20200328101708.png

查询Phoenix数据

QQ截图20200328101526.png

image.png

问题：MySQL->PHONEIX->presto

由于PHONEIX中存储是GMT时区，会差8个小时，。也是查询phoniex的话还得加8小时，后来presto中不支持TimeStamp，直接改为bigint类型
phoenix中的decimal类型在碰到类似1000的话会存储格式为科学记数法，比如1.32E+4
创建索引的时候，MySQL中的unique index 在phoenix中没有，只有CONSTRAINT pk PRIMARY KEY这种组合primary key
MySQL中bit改为了tinyInt类型存储在Phoenix中