開發與維運

數據庫混查 QuickSQL 0.7.0使用初體驗

2020-03-27

本文介紹quicksql0.7.0使用方法，先將安裝包qsql-0.7.0-install上傳至服務器。

配置運行環境

進入conf目錄，修改運行環境配置文件quicksql-env.sh
如下，根據實際情況配置java及spark環境變量

1.    #!/bin/bash    
2.    # This file is sourced when running quicksql programs  
3.    # Copy it as quicksql-env.sh and edit it that to configure quicksql  
4.    # Options read when launching programs  
5.    # export SPARK_HOME=  # [Required] - SPARK_HOME, to set spark home for quicksql running. quicksql needs spark 2.0 or above.  
6.    # export JAVA_HOME=   # [Required] - JAVA_HOME, to set java home for quicksql running. quicksql needs java 1.8 or above.  
7.    #配置java環境變量  
8.    export JAVA_HOME=/usr/java/jdk1.8.0_181-cloudera  
9.    #配置spark環境變量  
10.    export SPARK_HOME=/opt/cloudera/parcels/CDH-6.2.0-1.cdh6.2.0.p0.967373/lib/spark  
11.    # export FLINK_HOME=   # [Required] - FLINK_HOME, to set flink home for quicksql running. quicksql needs flink 1.9.0 or  
12.    # above.  
13.    # export QSQL_CLUSTER_URL=  # [Required] - QSQL_CLUSTER_URL, to set hadoop file system url.  
14.    # export QSQL_HDFS_TMP=   # [Required] - QSQL_HDFS_TMP, to set hadoop file system tmp url.  
15.    # Options read when using command line "quicksql.sh -e" with runner "Jdbc", "Spark" or "Dynamic" runner determined that  
16.    # this  
17.    # They all have default values. But we recommend you to set them here for more properly to your site.  
18.    # Those are default value for running a quicksql program if user does not set those properties.  
19.    # export QSQL_DEFAULT_WORKER_NUM=20      # [Optional] - QSQL_DEFAULT_WORKER_NUM, to set default worker_num for quicksql programs. if it is not set, default value is  
20.    # export QSQL_DEFAULT_WORKER_MEMORY=1G   # [Optional] - QSQL_DEFAULT_WORKER_MEMORY, to set default worker_memory for quicksql programs. if it is not set, default  
21.    # export QSQL_DEFAULT_DRIVER_MEMORY=3G   # [Optional] - QSQL_DEFAULT_DRIVER_MEMORY, to set default driver_memory for quicksql programs. if it is not set, default  
22.    # export QSQL_DEFAULT_MASTER=yarn-client # [Optional] - QSQL_DEFAULT_MASTER, to set default master for quicksql programs. if it is not set, default value is  
23.    # export QSQL_DEFAULT_RUNNER=DYNAMIC     # [Optional] - QSQL_DEFAULT_RUNNER, to set default master for quicksql programs. if it is not set, default value is dynamic.

配置完如下圖：
1.png

導入源數據庫表結構

quicksql目前支持Hive, MySQL, Kylin, Elasticsearch, Oracle, MongoDB等6種數據源導入，導入時使用bin/metadata-extract.sh腳本，語法如下：

1.    #<SCHEMA-JSON>包含數據源jdbc類型，url，連接賬號，密碼  
2.    #<DATA-SOURCE>為數據庫名稱  
3.    #<TABLE-NAME-REGEX>為數據表  
4.    $ ./bin/metadata-extract -p "<SCHEMA-JSON>" -d "<DATA-SOURCE>" -r "<TABLE-NAME-REGEX>"

其中，-r 參數可以使用LIKE語法，['%': 全部匹配，'_': 佔位匹配，'?': 可選匹配]）
本文以導入mysql數據和hive數據為例：

導入mysql數據，192.168.112.1:3306地址test數據庫的student表

1.    #導入192.168.112.1:3306地址test數據庫的student表  
2.    ./metadata-extract.sh -p "{\"jdbcDriver\": \"com.mysql.jdbc.Driver\", \"jdbcUrl\": \"jdbc:mysql://192.168.112.1:3306/test\", \"jdbcUser\": \"root\",\"jdbcPassword\": \"root\"}" -d "mysql" -r "student"

導入成功如下圖：
2.png

導入hive數據，如hive使用mysql作為元數據庫，則使用如下語句：

1.    #mysql作為hive元數據，metastore為hive元數據數據庫名稱，dbName為要導入的業務數據庫，studentt_ext為業務數據表，填寫正確的數據庫連接url，用戶名，密碼  
2.    ./metadata-extract.sh -p "{\"jdbcDriver\": \"com.mysql.jdbc.Driver\", \"jdbcUrl\": \"jdbc:mysql://192.168.112.180:3306/metastore\", \"jdbcUser\": \"hive\",\"jdbcPassword\": \"123456789\",\"dbName\": \"test2\"}" -d "hive" -r  "student_ext"

導入成功如下圖：
3.png

更多導入JSON如下：


1.    ##MySQL  
2.    {  
3.        "jdbcDriver": "com.mysql.jdbc.Driver",  
4.        "jdbcUrl": "jdbc:mysql://localhost:3306/db",  
5.        "jdbcUser": "USER",  
6.        "jdbcPassword": "PASSWORD"  
7.    }  
8.    ##Oracle  
9.    {  
10.        "jdbcDriver": "oracle.jdbc.driver.OracleDriver",  
11.        "jdbcUrl": "jdbc:oracle:thin:@localhost:1521/namespace",  
12.        "jdbcUser": "USER",  
13.        "jdbcPassword": "PASSWORD"   
14.    }  
15.    ##Elasticsearch  
16.    {  
17.        "esNodes": "192.168.1.1",  
18.        "esPort": "9000",  
19.        "esUser": "USER",  
20.        "esPass": "PASSWORD",  
21.        "esIndex": "index/type"  
22.    }  
23.    ##Hive(Hive元數據存在MySQL中)  
24.    {  
25.        "jdbcDriver": "com.mysql.jdbc.Driver",  
26.        "jdbcUrl": "jdbc:mysql://localhost:3306/db",  
27.        "jdbcUser": "USER",  
28.        "jdbcPassword": "PASSWORD",  
29.        "dbName": "hive_db"  
30.    }  
31.    ##Hive-Jdbc(Hive元數據通過Jdbc訪問 )  
32.    {  
33.        "jdbcDriver": "org.apache.hive.jdbc.HiveDriver",  
34.        "jdbcUrl": "jdbc:hive2://localhost:7070/learn_kylin",  
35.        "jdbcUser": "USER",  
36.        "jdbcPassword": "PASSWORD",  
37.        "dbName": "default"  
38.    }  
39.    ##Kylin  
40.    {  
41.        "jdbcDriver": "org.apache.kylin.jdbc.Driver",  
42.        "jdbcUrl": "jdbc:kylin://localhost:7070/learn_kylin",  
43.        "jdbcUser": "ADMIN",  
44.        "jdbcPassword": "KYLIN",  
45.        "dbName": "default"  
46.    }  
47.    ##Mongodb  
48.    {  
49.        "host": "192.168.1.1",  
50.        "port": "27017",  
51.        "dataBaseName": "test",  
52.        "authMechanism": "SCRAM-SHA-1",  
53.        "userName": "admin",  
54.        "password": "admin",  
55.        "collectionName": "products"  
56.    }

注意：Shell中雙引號是特殊字符，傳JSON參數時需要做轉義！！

Shell腳本實現混查

使用bin/ quicksql.sh腳本進行查詢，語法如下：

1.    ./quicksql.sh -e "YOU SQL";

查詢mysql上的單表 student

1.    ./quicksql.sh -e "select * from student";

查詢結果如下圖：
4.png

查詢hive上的單表 student_ext

1.    ./quicksql.sh -e "select * from student_ext";

5.png

兩表left join 混查:

1.    ./quicksql.sh -e "select * from student as a left join student_ext as b on a.id = b.id";

查詢結果：
6.png

使用java編寫程序實現客戶端JDBC調用

啟動quicksql-server服務，運行 bin/quicksql-server.sh

1.    ./quicksql-server.sh start

啟動成功如下圖：
1.png

通訊端口為5888，也可使用./quicksql-server.sh start | restart | status | stop 進行進行其他操作。

編寫java程序，引入依賴

1.    #qsql-client通過本地方式引用，qsql-client-0.7.0.jar文件可從qsql-0.7.0-install/lib下複製過來  
2.    <dependency>  
3.          <groupId>com.qihoo.qsql</groupId>  
4.          <artifactId>qsql</artifactId>  
5.          <version>0.7.0</version>  
6.          <scope>system</scope>  
7.          <systemPath>${project.basedir}/qsql-client-0.7.0.jar</systemPath>  
8.    </dependency>  
9.    <dependency>  
10.          <groupId>org.apache.calcite.avatica</groupId>  
11.          <artifactId>avatica-server</artifactId>  
12.          <version>1.12.0</version>  
13.    </dependency>

如下圖：
2.png

jdbc連接地址按實際情況配置。

運行結果如下：
3.png

Leave a Reply

Your email address will not be published. Required fields are marked *