课程1：学习数据集

目标

本课简单的介绍什么数据可用，格式是什么，并使用简单的 SELECT 这样的 SQL 语句。Drill 可以在未知的情况下，有能力去分析数据，并能够去定义它的 Schema。这意味着，你可以立即开始查询数据（尽管它更改了），而不用在乎它的格式。

本指南所包含的数据集由以下组成：

  • 事务数据：存储在 Hive 表
  • 商品的 catalog 和客户数据：存储在 MapR-DB 表中
  • 点击日志数据：以 JSON 文件的形式，存储在 MapR 的文件系统中

本课的查询

本课将使用 select * 去查询每个数据源。

开始之前

打开 Drill Shell

如果你的 Drill Shell 还没有启动，使用终端或 CMD 在虚拟机中打开，然后输入 sqlline，在 在 Sandbox 中使用 Drill 一节中由详细的介绍。

你可以在终端中完成查询。最后退出 Drill Shell，命令如下所示：

  1. 0: jdbc:drill:> !quit

本示例是在终端中使用 Drill Shell。你也可以使用 Drill Web 控制台去完成查询。

启用 DECIMAL 数据类型

终端中演示的是使用 DECIMAL 数据类型的示例。DECIMAL 数据类型默认是禁止的在发布的版本当中，所以，在处理之前需要我们去开启 DECIMAL 数据类型。

  1. alter session set `planner.enable_decimal_data_type`=true;
  3. +-------+--------------------------------------------+
  4. |  ok   |                  summary                   |
  5. +-------+--------------------------------------------+
  6. | true  | planner.enable_decimal_data_type updated.  |
  7. +-------+--------------------------------------------+
  8. 1 row selected

显示可用的数据库

  1. 0: jdbc:drill:> show databases;
  2. +---------------------+
  3. |     SCHEMA_NAME     |
  4. +---------------------+
  5. | INFORMATION_SCHEMA  |
  6. | cp.default          |
  7. | dfs.clicks          |
  8. | dfs.default         |
  9. | dfs.logs            |
  10. | dfs.root            |
  11. | dfs.tmp             |
  12. | dfs.views           |
  13. | hive.default        |
  14. | maprdb              |
  15. | sys                 |
  16. +---------------------+

该命令展示了在存储插件中，所有可用的元数据。文件系统中配置的 Hive，MapR-DB 数据库，文件系统或是其他的数据。当你在本指南中运行查询的时候，你可以使用 USE 命令去做切换。这样的方式和关系型数据库中的切换不同的数据库很类似。

查询 Hive 表

订单表是一个 6 列的 Hive 表在 Hive 元数据中。这个 Hive 的外部表指向存储在 MapR 文件系统中的数据。订单表包含 122000 行记录。

设置 hive 的 schema：

  1. 0: jdbc:drill:> use hive.`default`;
  2. +-------+-------------------------------------------+
  3. |  ok   |                  summary                  |
  4. +-------+-------------------------------------------+
  5. | true  | Default schema changed to [hive.default]  |
  6. +-------+-------------------------------------------+
  7. 1 row selected

你可以在终端中使用 USE 命令。使用 USE 命令设置 schema 到当前的会话中。

表描述

你也可以使用 DESCRIBE 命令去显示 Hive 表中的列和数据类型：

  1. 0: jdbc:drill:> describe orders;
  2. +-------------+------------+-------------+
  3. | COLUMN_NAME | DATA_TYPE  | IS_NULLABLE |
  4. +-------------+------------+-------------+
  5. | order_id    | BIGINT     | YES         |
  6. | month       | VARCHAR    | YES         |
  7. | cust_id     | BIGINT     | YES         |
  8. | state       | VARCHAR    | YES         |
  9. | prod_id     | BIGINT     | YES         |
  10. | order_total | INTEGER    | YES         |
  11. +-------------+------------+-------------+

对于 DESCRIBE 命令，返回的是 Hive 表中 schema 的相关信息。

从订单表中获取 5 条记录

  1. 0: jdbc:drill:> select * from orders limit 5;
  2. +------------+------------+------------+------------+------------+-------------+
  3. |  order_id  |   month    |  cust_id   |   state    |  prod_id   | order_total |
  4. +------------+------------+------------+------------+------------+-------------+
  5. | 67212      | June       | 10001      | ca         | 909        | 13          |
  6. | 70302      | June       | 10004      | ga         | 420        | 11          |
  7. | 69090      | June       | 10011      | fl         | 44         | 76          |
  8. | 68834      | June       | 10012      | ar         | 0          | 81          |
  9. | 71220      | June       | 10018      | az         | 411        | 24          |
  10. +------------+------------+------------+------------+------------+-------------+

因为订单表是一个 Hive 表，你可以查询数据用和查询关系型数据库表一样的方式去查询。注意使用标准的 LIMIT 语法，返回的数据集结果是从最开始的行到指定的行。你可以使用 LIMIT 和 ORDER BY 进行组合。另外，用 LIMIT 5,10 这样的分页语句，在 Drill 中是不能直接用的，需要转变写法，在后面的章节会讲到。

Drill 可以无缝的集成到 Hive 上，并允许去查询 Hive 表的元数据，无需额外的配置。对于 Drill 来说，Hive 不是先决条件。但是可以简单的作为存储插件或数据源服务于 Drill。Drill 允许用户查询所有的 Hive 文件格式（包括自定义的SerDes）。另外，任何 UDFS 函数都可以作为 Drill 查询的一部分来使用。

因为 Drill 是一个属于低延时的 SQL 查询引擎，你可以查询 Hive 表。它具有高性能，支持交互式和临时的数据探索。

查询 MapR-DB 和 HBase 表

商品和客户的表都是 MapR-DB 表。MapR-DB 是一个企业级的，基于 Hadoop 的 NoSQL 数据库。它支持 HBase API 应用开发。每个 MapR-DB 表有一个 row_key，另外，有一个或多个列簇。每个列簇包含一个或多个指定的列。row_key 被定义为主键用于每一个行记录。

Drill 可以直接查询 MapR-DB 和 HBase 表。不同与其他 Hadoop 选择 SQL，Drill 在处理数据的时候需要，无覆盖的 Schema 定义与 Hive。当你的 MapR-DB 和 HBase 表中上千个列在一个时间序列数据库中，Drill 会从 Hive 中移除正处理管理的 Schemas。

商品表

商品表有两个列簇。

  1. +--------------+---------+
  2. |Column Family | Columns |
  3. +--------------+---------+
  4. |details       | name    |
  5. |              | category|
  6. +--------------+---------+
  7. |pricing       | price   |
  8. +--------------+---------+

商品表包含 965 条记录。

切换到 maprdb 的工作空间

  1. use maprdb;
  2. +-------+-------------------------------------+
  3. |  ok   |               summary               |
  4. +-------+-------------------------------------+
  5. | true  | Default schema changed to [maprdb]  |
  6. +-------+-------------------------------------+
  7. 1 row selected

表描述

  1. 0: jdbc:drill:> describe customers;
  2. +--------------+------------------------+--------------+
  3. | COLUMN_NAME  |       DATA_TYPE        | IS_NULLABLE  |
  4. +--------------+------------------------+--------------+
  5. | row_key      | ANY                    | NO           |
  6. | address      | (VARCHAR(1), ANY) MAP  | NO           |
  7. | loyalty      | (VARCHAR(1), ANY) MAP  | NO           |
  8. | personal     | (VARCHAR(1), ANY) MAP  | NO           |
  9. +--------------+------------------------+--------------+
  10. 4 rows selected
  12. 0: jdbc:drill:> describe products;
  13. +--------------+------------------------+--------------+
  14. | COLUMN_NAME  |       DATA_TYPE        | IS_NULLABLE  |
  15. +--------------+------------------------+--------------+
  16. | row_key      | ANY                    | NO           |
  17. | details      | (VARCHAR(1), ANY) MAP  | NO           |
  18. | pricing      | (VARCHAR(1), ANY) MAP  | NO           |
  19. +--------------+------------------------+--------------+
  20. 3 rows selected

与 Hive 不同的是，该命令不会将完整的 Schema 返回到列级。对于 NoSQL 数据库来说，例如 MapR-DB 和 HBase 能够将 Schema 设计的很少。每一行都有自己的列将自己的值给到自己所属的列簇中，并且列的值能够是任意数据类型，由应该程序去确定数据的类型。

一个复杂的 “MAP” 集合类型，在 Drill 呈现的方式就是名称和值的结构（K/V），事实上，列的值可以成为任何数据类型。通过观察 row_key，它也是一个简单的字节或是任何类型。

从商品表中选择 5 行记录：

  1. 0: jdbc:drill:> select * from products limit 5;
  2. +--------------+----------------------------------------------------------------------------------------------------------------+-------------------+
  3. |   row_key    |                                                    details                                                     |      pricing      |
  4. +--------------+----------------------------------------------------------------------------------------------------------------+-------------------+
  5. | [B@b01c5f8   | {"category":"bGFwdG9w","name":"U29ueSBub3RlYm9vaw=="}                                                          | {"price":"OTU5"}  |
  6. | [B@5edfe5ad  | {"category":"RW52ZWxvcGVz","name":"IzEwLTQgMS84IHggOSAxLzIgUHJlbWl1bSBEaWFnb25hbCBTZWFtIEVudmVsb3Blcw=="}      | {"price":"MTY="}  |
  7. | [B@3d5ff184  | {"category":"U3RvcmFnZSAmIE9yZ2FuaXphdGlvbg==","name":"MjQgQ2FwYWNpdHkgTWF4aSBEYXRhIEJpbmRlciBSYWNrc1BlYXJs"}  | {"price":"MjEx"}  |
  8. | [B@65e93096  | {"category":"TGFiZWxz","name":"QXZlcnkgNDk4"}                                                                  | {"price":"Mw=="}  |
  9. | [B@3074fc1f  | {"category":"TGFiZWxz","name":"QXZlcnkgNDk="}                                                                  | {"price":"Mw=="}  |
  10. +--------------+----------------------------------------------------------------------------------------------------------------+-------------------+
  11. 5 rows selected

鉴于 Drill 不需要事先定义 Schema 去表示数据类型，查询返回的列值是字节数组，这是因为它们存储在 MapR-DB（或 HBase）。通过观察列簇（details 和 pricing）由 MAP 数据类型和 JSON 字符串。

在第 2 课中，你将会使用 CAST 函数去获取返回的数据类型。

从客户表中获取 5 行记录

  2. +0: jdbc:drill:> select * from customers limit 5;
  3. +--------------+-----------------------+-------------------------------------------------+---------------------------------------------------------------------------------------+
  4. |   row_key    |        address        |                     loyalty                     |                                       personal                                        |
  5. +--------------+-----------------------+-------------------------------------------------+---------------------------------------------------------------------------------------+
  6. | [B@3ed2649e  | {"state":"InZhIg=="}  | {"agg_rev":"MTk3","membership":"InNpbHZlciI="}  | {"age":"IjE1LTIwIg==","gender":"IkZFTUFMRSI=","name":"IkNvcnJpbmUgTWVjaGFtIg=="}      |
  7. | [B@66cbe14a  | {"state":"ImluIg=="}  | {"agg_rev":"MjMw","membership":"InNpbHZlciI="}  | {"age":"IjI2LTM1Ig==","gender":"Ik1BTEUi","name":"IkJyaXR0YW55IFBhcmsi"}              |
  8. | [B@5333f5ff  | {"state":"ImNhIg=="}  | {"agg_rev":"MjUw","membership":"InNpbHZlciI="}  | {"age":"IjI2LTM1Ig==","gender":"Ik1BTEUi","name":"IlJvc2UgTG9rZXki"}                  |
  9. | [B@785b6305  | {"state":"Im1lIg=="}  | {"agg_rev":"MjYz","membership":"InNpbHZlciI="}  | {"age":"IjUxLTEwMCI=","gender":"IkZFTUFMRSI=","name":"IkphbWVzIEZvd2xlciI="}          |
  10. | [B@37c21afe  | {"state":"Im1uIg=="}  | {"agg_rev":"MjAy","membership":"InNpbHZlciI="}  | {"age":"IjUxLTEwMCI=","gender":"Ik9USEVSIg==","name":"Ikd1aWxsZXJtbyBLb2VobGVyIg=="}  |
  11. +--------------+-----------------------+-------------------------------------------------+---------------------------------------------------------------------------------------+
  12. 5 rows selected

另外，表返回的字节数据是需要转换未可读的数据类型的。

查询文件系统

随着插叙数据源以全 Schema（例如 Hive）和部分 Schema（例如 MapR-DB 和 HBase），Drill 提供了直接在文件系统处理 SQL 查询的能力。文件系统一般有本地文件系统，分布式文件系统，例如 MapR-FS，HDFS 或是 S3。

在 Drill 中，一个文件或是一个目录代表一个关系型数据库中的“表”。因此，你可以直接执行 SQL 用于文件和目录。Schema 是基于查询的基础上发现的。而且 Drill 支持多种文件格式，其中包含 text，CSV，Parquet 和 JSON。

下面的例子，是来源于手机端/网页端应用的 JSON 格式的点击量数据。JSON 文件格式如下所示：

  1. {"trans_id":31920,"date":"2014-04-26","time":"12:17:12","user_info":{"cust_id":22526,"device":"IOS5","state":"il"},"trans_info":{"prod_id":[174,2],"purch_flag":"false"}}
  2. {"trans_id":31026,"date":"2014-04-20","time":"13:50:29","user_info":{"cust_id":16368,"device":"AOS4.2","state":"nc"},"trans_info":{"prod_id":[],"purch_flag":"false"}}
  3. {"trans_id":33848,"date":"2014-04-10","time":"04:44:42","user_info":{"cust_id":21449,"device":"IOS6","state":"oh"},"trans_info":{"prod_id":[582],"purch_flag":"false"}}

clicks.json 和 clicks.campaign.json 文件包含元数据作为数据本身的一部分（称为“自我描述”的数据）。数据元素本身复杂复杂，还做了嵌套。初始化查询不显示如何去解析嵌套数据，但是它们会很容易的进入到数据，而不需要去工作空间定义。

查询嵌套点击量数据

切换到 dfs.clicks 工作空间：

  1. 0: jdbc:drill:> use dfs.clicks;
  2. +-------+-----------------------------------------+
  3. |  ok   |                 summary                 |
  4. +-------+-----------------------------------------+
  5. | true  | Default schema changed to [dfs.clicks]  |
  6. +-------+-----------------------------------------+
  7. 1 row selected

在这种情况下，设置工作区间使查询变得更加容易。当你指定一个文件系统到工作区间，你可以缩短你的查询路径（指向变短）。而不必为一个文件提供完整的路径，你可以提供一个文件的相对路径，例如：

  1. "location": "/mapr/demo.mapr.com/data/nested"

在这个相对路径下，任何文件和目录你都可以去查询。下面的查询所指的是直接在目录中嵌套。

从 clicks.json 文件中选择 2 行记录：

  1. 0: jdbc:drill:> select * from `clicks/clicks.json` limit 2;
  2. +-----------+-------------+-----------+---------------------------------------------------+-------------------------------------------+
  3. | trans_id  |    date     |   time    |                     user_info                     |                trans_info                 |
  4. +-----------+-------------+-----------+---------------------------------------------------+-------------------------------------------+
  5. | 31920     | 2014-04-26  | 12:17:12  | {"cust_id":22526,"device":"IOS5","state":"il"}    | {"prod_id":[174,2],"purch_flag":"false"}  |
  6. | 31026     | 2014-04-20  | 13:50:29  | {"cust_id":16368,"device":"AOS4.2","state":"nc"}  | {"prod_id":[],"purch_flag":"false"}       |
  7. +-----------+-------------+-----------+---------------------------------------------------+-------------------------------------------+
  8. 2 rows selected

这个 FROM 语句执行一个特定的文件。Drill 扩展了传统的表概念。它对比传统的 SQL 中的 FROM，能够适合一个本地文件或是分布式文件系统。

唯一的要求是需要我们去可以封装文件路径。这个是有必要的，避免于 Drill 的保留字符冲突。

从 campaign.json 文件中选择 2 行记录：

  1. 0: jdbc:drill:> select * from `clicks/clicks.campaign.json` limit 2;
  2. +-----------+-------------+-----------+---------------------------------------------------+---------------------+----------------------------------------+
  3. | trans_id  |    date     |   time    |                     user_info                     |       ad_info       |               trans_info               |
  4. +-----------+-------------+-----------+---------------------------------------------------+---------------------+----------------------------------------+
  5. | 35232     | 2014-05-10  | 00:13:03  | {"cust_id":18520,"device":"AOS4.3","state":"tx"}  | {"camp_id":"null"}  | {"prod_id":[7,7],"purch_flag":"true"}  |
  6. | 31995     | 2014-05-22  | 16:06:38  | {"cust_id":17182,"device":"IOS6","state":"fl"}    | {"camp_id":"null"}  | {"prod_id":[],"purch_flag":"false"}    |
  7. +-----------+-------------+-----------+---------------------------------------------------+---------------------+----------------------------------------+
  8. 2 rows selected

需要注意的是，在使用 select * 做查询的适合，一些复杂的数据类型，例如 maps 和 arrays 会返回一个 JSON 字符串。在下一课，你将会明白利用不同的 SQL 函数 去解析这些数据。

查询日志数据

不像之前的例子，我们是在一个文件中对点击数进行查询。日志数据被存放在文件系统的分区目录。这里日志目录有三个子目录：

  • 2012
  • 2013
  • 2014

这些年的数据目录由一组组的月目录构成，每个月目录包含一个 JSON 文件，里面包含了日志记录。所有的日志文件的记录总数为 48000。

在日志文件目录和子目录下的都是 JSON 文件。这些文件比较多，但是你只需要使用 Drill 去查询单个数据源或者查询子文件。

切换到 dfs.logs 工作空间：

  1. 0: jdbc:drill:> use dfs.logs;
  2. +-------+---------------------------------------+
  3. |  ok   |                summary                |
  4. +-------+---------------------------------------+
  5. | true  | Default schema changed to [dfs.logs]  |
  6. +-------+---------------------------------------+
  7. 1 row selected

从日志目录中选择 2 行记录：

  1. 0: jdbc:drill:> select * from logs limit 2;
  2. +-------+-------+-----------+-------------+-----------+----------+---------+--------+----------+-----------+----------+-------------+
  3. | dir0  | dir1  | trans_id  |    date     |   time    | cust_id  | device  | state  | camp_id  | keywords  | prod_id  | purch_flag  |
  4. +-------+-------+-----------+-------------+-----------+----------+---------+--------+----------+-----------+----------+-------------+
  5. | 2012  | 8     | 109       | 08/07/2012  | 20:33:13  | 144618   | IOS5    | ga     | 4        | hey       | 6        | false       |
  6. | 2012  | 8     | 119       | 08/19/2012  | 03:37:50  | 17       | IOS5    | tx     | 16       | and       | 50       | false       |
  7. +-------+-------+-----------+-------------+-----------+----------+---------+--------+----------+-----------+----------+-------------+
  8. 2 rows selected

注意，这些都是 JSON 数据。dfs.clicks 的工作区间指向一个目录，其中包含一个日志目录，就是使用 FROM 语法做查询时，指向的目录。在文件系统中，你不需要指名完整的目录路径。

列名 dir0 和 dir1 是 Drill 表示可变变量的一种方式。在第 3 课中，你将利用这些动态变量，做更复杂的查询。

统计所有文件的总行数（所有的文件）：

  1. 0: jdbc:drill:> select count(*) from logs;
  2. +---------+
  3. | EXPR$0  |
  4. +---------+
  5. | 48000   |
  6. +---------+
  7. 1 row selected

此查询遍历所有的文件在日志目录及其子目录中的文件，并返回行数.