GitHub - Qihoo360/Quicksql: Simpler, Safer, Faster Unified SQL Analytics Engine...

README.md

QSQL is a SQL query product which can be used for specific datastore queries or multiple datastores correlated queries. It supports relational databases, non-relational databases and even datastore which does not support SQL (such as Elasticsearch, Druid) . In addition, a SQL query can join or union data from multiple datastores in QSQL. For example, you can perform unified SQL query on one situation that a part of data stored on Elasticsearch, but the other part of data stored on Hive. The most important is that QSQL is not dependent on any intermediate compute engine, users only need to focus on data and unified SQL grammar to finished statistics and analysis.

English|中文

QSQL architecture consists of three layers：

• Parsing Layer: Used for parsing, validation, optimization of SQL statements, splitting of mixed SQL and finally generating Query Plan;

• Computing Layer: For routing query plan to a specific execution plan, then interpreted to executable code for given storage or engine(such as Elasticsearch JSON query or Hive HQL);

• Storage layer: For data prepared extraction and storage;

Build

Requirements

• CentOS 6.2
• java >= 1.8
• scala >= 2.11
• spark >= 2.2
• [Options] MySQL, Elasticsearch, Hive, Druid

Deployment

Uncompress the package qsql-0.5.tar.gz

tar -zxvf ./qsql-0.5.tar.gz

Create a soft link

ln -s qsql-0.5/ qsql

The main directory structure after decompression of the release package is：

• bin: included all of scripts for building environment and running sql.
• conf: included all of configures in runtime.
• data: stored data for testing.
• metastore: included a embedded database and create table statements scripts for managing metadata.

In directory $QSQL_HOME/conf, configure the following files：

• base-env.sh：Included correlated environment variables：
  • JAVA_HOME
  • SPARK_HOME
  • QSQL_CLUSTER_URL
  • QSQL_HDFS_TMP
• qsql-runner.properties：Included serveral runtime properties
• log4j.properties：Included logger level

Examples

QSQL Shell

./bin/qsql -e "select 1"

Detailed：English|中文

Query Example

Several sample queries are included with QSQL. To run one of them, use ./run-example <class> [params]

Example 1: Memory Table Query

./bin/run-example com.qihoo.qsql.CsvScanExample

Example 2: Hive Join MySQL

./bin/run-example com.qihoo.qsql.CsvJoinWithEsExample

Note:

If you are running a hybrid query, make sure the current machine has deployed Spark, Hive and MySQL environment and inserted the correct connection information of Hive and MySQL into the metastore.

Detailed：English|中文

Properties Configure

Environment Variables

Runtime Variables

Application Properties

Metadata Properties

Metadata Management

Tables

DBS

DATABASE_PARAMS

TBLS

COLUMNS

Embedded SQLite Database

In the directory $QSQL_HOME/metastore, included following files：

• sqlite3：SQLite command line tool
• schema.db：SQLite embedded database
• ./linux-x86/sqldiff：A tool that displays the differences between SQLite databases.
• ./linux-x86/sqlite3_analyzer：A command-line utility program that measures and displays how much and how efficiently space is used by individual tables and indexes with an SQLite database file

Connect to the schema.db database via sqlite3 and manipulate the metadata table

sqlite3 ../schema.db

External MySQL Database

Change the embedded SQLite data to a MySQL database

vim metadata.properties

meta.storage.mode=extern meta.extern.schema.driver = com.mysql.jdbc.Driver meta.extern.schema.url = jdbc:mysql://ip:port/db?useUnicode=true meta.extern.schema.user = YourName meta.extern.schema.password = YourPassword

Initialize the sample data to the MySQL database

cd $QSQL_HOME/bin/
./metadata --dbType mysql --action init

Configure Metadata

Hive

Sample Configuration：

DB_ID DESC NAME DB_TYPE 26 hive message hive_database hive DB_ID PARAM_KEY PARAM_VALUE 26 cluster cluster_name TBL_ID CREATE_TIME DB_ID TBL_NAME 60 2018-11-06 10:44:51 26 hive_mobile CD_ID COMMENT COLUMN_NAME TYPE_NAME INTEGER_IDX 60

retsize string 1 60

im string 2 60

wto string 3 60

pro int 4 60

pday string 5

Elasticsearch

Sample Configuration：

MySQL

Sample Configuration：

Contributing

We welcome contributions.

If you are intered in QSQL, you can download the source code from GitHub and execute the following maven comman at the project root directory：

mvn -DskipTests clean package

If you are planning to make a large contribution, talk to us first! It helps to agree on the general approach. Log a Issures on GitHub for your proposed feature.

Fork the GitHub repository, and create a branch for your feature.

Develop your feature and test cases, and make sure that mvn install succeeds. (Run extra tests if your change warrants it.)

Commit your change to your branch.

If your change had multiple commits, use git rebase -i master to squash them into a single commit, and to bring your code up to date with the latest on the main line.

Then push your commit(s) to GitHub, and create a pull request from your branch to the QSQL master branch. Update the JIRA case to reference your pull request, and a committer will review your changes.

The pull request may need to be updated (after its submission) for two main reasons:

1. you identified a problem after the submission of the pull request;
2. the reviewer requested further changes;

In order to update the pull request, you need to commit the changes in your branch and then push the commit(s) to GitHub. You are encouraged to use regular (non-rebased) commits on top of previously existing ones.

Talks

QQ Group: 932439028

WeChat Group: Posted in the QQ Group [ P.S. Incorrect QQ Group number will raise a NPE :) ]