本文介紹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. 配置完如下圖：
導入源資料庫表結構
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" 導入成功如下圖：
導入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" 更多導入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"; 查詢結果如下圖：
查詢hive上的單表 student_ext
1. ./quicksql.sh -e "select * from student_ext"; 兩表left join 混查:
1. ./quicksql.sh -e "select * from student as a left join student_ext as b on a.id = b.id"; 查詢結果：
使用java編寫程式實作用戶端JDBC調用
啟動quicksql-server服務，運作 bin/quicksql-server.sh
1. ./quicksql-server.sh start 啟動成功如下圖：
通訊端口為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> 如下圖：
jdbc連接配接位址按實際情況配置。
運作結果如下：