学习钻MapR Sandbox Lesson1:了解数据集《译》
目标：
这节课是讲发现数据在什么格式是可用的，使用简单的SQL SELECT语句声明。钻可以不用先验知识和定义它的模式来分析数据。这意味着您可以立即开始查询数据(即使它改变)，不用管它的格式。
教程的数据集包括：
事务性数据：存储为一个hive表（不变的数据）
产品目录和掌握客户数据：存储为MapR-DB表（变化的数据）
点击流和日志数据：MapR文件系统存储为JSON文件
查询在这节
这节课由每个数据源的查询selecet * 组成
在你开始之前
开始钻壳
如果钻壳尚未启动，使用Terminal或者命令提示符用mapr去记录到VM演示，然后sqlline回车；像http://drill.apache.org/docs/getting-to-know-the-drill-sandbox/中描述的一样。
退出钻壳:
0: jdbc:drill:> !quit
本教程示例使用钻壳。您也可以使用钻Web控制台执行查询。
启用decimal数据类型
本教程使用DECIMAL数据类型的一些例子。DECIMAL数据类型在这个版本中默认是禁用的,所以在继续之前启用DECIMAL数据类型:
先用mapr账户远程登录到沙箱，然后sqlline启动钻开始查询。又开启DECIMAL数据类型：
zj@zj:~$ ssh mapr@localhost -p 2222 0: jdbc:drill:> alter session set 'planner.enable_decimal_data_type'=true;
列出可用的工作区和数据库：
0: jdbc:drill:> show databases; +---------------------+ | SCHEMA_NAME | +---------------------+ | INFORMATION_SCHEMA | | cp.default | | dfs.clicks | | dfs.default | | dfs.logs | | dfs.root | | dfs.tmp | | dfs.views | | hive.default | | maprdb | | sys | +---------------------+
这个命令显示所有可用的元数据从存储插件配置了钻作为一组模式。hive和MapR-DB数据库、文件系统、和其他在文件系统中配置的数据。做为你在本教程中运行查询,运行USE命令在这些模式之间切换。用这种方法转换模式类似于在关系数据库中使用不同的数据库模式(命名空间)。
查询hive表
订单表是一个在hive元存储中定义6列的hive表。这是一个hive外部表指向存储在MapR文件系统上平面文件中的数据。这个订单表包含122000行。
hive模式设置:
0: jdbc:drill:> use hive.`default`; +-------+-------------------------------------------+ | ok | summary | +-------+-------------------------------------------+ | true | Default schema changed to [hive.default] | +-------+-------------------------------------------+ 1 row selected
你将运行USE命令贯穿这个教程中。USE命令设置当前会议模式。
描述表:
你可以使用DESCRIBE命令显示Hive表的列和数据类型：
0: jdbc:drill:> describe orders; +-------------+------------+-------------+ | COLUMN_NAME | DATA_TYPE | IS_NULLABLE | +-------------+------------+-------------+ | order_id | BIGINT | YES | | month | VARCHAR | YES | | cust_id | BIGINT | YES | | state | VARCHAR | YES | | prod_id | BIGINT | YES | | order_total | INTEGER | YES | +-------------+------------+-------------+
DESCRIBE命令返回hive表的完整模式信息基于在hive元存储中可利用的元数据。
从订单表选择5行:
0: jdbc:drill:> select * from orders limit 5; +------------+------------+------------+------------+------------+-------------+ | order_id | month | cust_id | state | prod_id | order_total | +------------+------------+------------+------------+------------+-------------+ | 67212 | June | 10001 | ca | 909 | 13 | | 70302 | June | 10004 | ga | 420 | 11 | | 69090 | June | 10011 | fl | 44 | 76 | | 68834 | June | 10012 | ar | 0 | 81 | | 71220 | June | 10018 | az | 411 | 24 | +------------+------------+------------+------------+------------+-------------+
因为订单是一个hive表,你可以以同样的方式查询数据，你可以查询关系数据库表中的列。注意使用标准的LIMIT条款,限制结果集规定行的数量。您可以使用限制有或没有ORDER BY子句。
钻提供了与hive的无缝集成,允许查询hive表定义在元存储中无需额外配置。hive不是钻的先决条件,但只是作为钻的一个存储插件或数据源。钻还允许用户查询所有的hive文件格式(包括hive 自定义并行转换器)。此外,一些定义在hive中的UDFs可以做为钻查询的一部分。
因为钻有自己的低延迟SQL查询执行引擎,你可以用具有高性能和支持交互式功能的hive查询和进行特殊表的数据探究。
查询MapR-DB和HBase表
客户表和产品表是MapR-DB表。MapR-DB是在hadoop NoSQL的企业数据库。它使HBase API来支持应用程序发展。每个MapR-DB表有一个row_key,除了一个或多个列的家庭。家庭每一列包含一个或多个特定列。row_key值是一个主键,唯一地标识每一行。
钻直接查询MapR-DB和HBase表。不像其他SQL Hadoop的选项,在hive中钻不需要叠加定义模式去和这些数据工作。当你MapR-DB或HBase表有成千上万的典型列时间序列数据库，钻删除重复的疼痛管理模式在hive表里。
产品表
产品表有两列的家庭。
产品表包含965行。
客户表
客户表有三列的家庭。
客户表包含993行。
设置maprdb工作区:
use maprdb; +-------+-------------------------------------+ | ok | summary | +-------+-------------------------------------+ | true | Default schema changed to [maprdb] | +-------+-------------------------------------+ 1 row selected
描述表:
0: jdbc:drill:> describe customers; +--------------+------------------------+--------------+ | COLUMN_NAME | DATA_TYPE | IS_NULLABLE | +--------------+------------------------+--------------+ | row_key | ANY | NO | | address | (VARCHAR(1), ANY) MAP | NO | | loyalty | (VARCHAR(1), ANY) MAP | NO | | personal | (VARCHAR(1), ANY) MAP | NO | +--------------+------------------------+--------------+ 4 rows selected 0: jdbc:drill:> describe products; +--------------+------------------------+--------------+ | COLUMN_NAME | DATA_TYPE | IS_NULLABLE | +--------------+------------------------+--------------+ | row_key | ANY | NO | | details | (VARCHAR(1), ANY) MAP | NO | | pricing | (VARCHAR(1), ANY) MAP | NO | +--------------+------------------------+--------------+ 3 rows selected
不像hive的例子,描述命令不返回完整的模式列。宽栏NoSQL数据库如MapR-DB和HBase可以通过模式设计;每一行对给定的列族都有自己设置的一组列名称,列值可以是任何数据类型,由应用程序插入数据定义。
在drill中复杂的“地图”类型表示该变量列名称结构,“任何”代表了事实的列值可以是任何数据类型。观察row_key也是简单的字节数和类型任何。
从产品表:选择5行
0: jdbc:drill:> select * from products limit 5;
+--------------+----------------------------------------------------------------------------------------------------------------+-------------------+
| row_key | details | pricing |
+--------------+----------------------------------------------------------------------------------------------------------------+-------------------+
| [B@b01c5f8 | {"category":"bGFwdG9w","name":"U29ueSBub3RlYm9vaw=="} | {"price":"OTU5"} |
| [B@5edfe5ad | {"category":"RW52ZWxvcGVz","name":"IzEwLTQgMS84IHggOSAxLzIgUHJlbWl1bSBEaWFnb25hbCBTZWFtIEVudmVsb3Blcw=="} | {"price":"MTY="} |
| [B@3d5ff184 | {"category":"U3RvcmFnZSAmIE9yZ2FuaXphdGlvbg==","name":"MjQgQ2FwYWNpdHkgTWF4aSBEYXRhIEJpbmRlciBSYWNrc1BlYXJs"} | {"price":"MjEx"} |
| [B@65e93096 | {"category":"TGFiZWxz","name":"QXZlcnkgNDk4"} | {"price":"Mw=="} |
| [B@3074fc1f | {"category":"TGFiZWxz","name":"QXZlcnkgNDk="} | {"price":"Mw=="} |
+--------------+----------------------------------------------------------------------------------------------------------------+-------------------+
5 rows selected鉴于钻查询不需要预先定义模式指明数据类型,查询返回原始字节数组的列值,就像他们存储在MapR-DB(或HBase)。观察到列家庭(细节和定价)拥有地图数据类型和表现为JSON字符串。
在第2课中,您将使用CAST函数为每一列返回类型数据。
从客户表选则5行:
+0: jdbc:drill:> select * from customers limit 5;
+--------------+-----------------------+-------------------------------------------------+---------------------------------------------------------------------------------------+
| row_key | address | loyalty | personal |
+--------------+-----------------------+-------------------------------------------------+---------------------------------------------------------------------------------------+
| [B@3ed2649e | {"state":"InZhIg=="} | {"agg_rev":"MTk3","membership":"InNpbHZlciI="} | {"age":"IjE1LTIwIg==","gender":"IkZFTUFMRSI=","name":"IkNvcnJpbmUgTWVjaGFtIg=="} |
| [B@66cbe14a | {"state":"ImluIg=="} | {"agg_rev":"MjMw","membership":"InNpbHZlciI="} | {"age":"IjI2LTM1Ig==","gender":"Ik1BTEUi","name":"IkJyaXR0YW55IFBhcmsi"} |
| [B@5333f5ff | {"state":"ImNhIg=="} | {"agg_rev":"MjUw","membership":"InNpbHZlciI="} | {"age":"IjI2LTM1Ig==","gender":"Ik1BTEUi","name":"IlJvc2UgTG9rZXki"} |
| [B@785b6305 | {"state":"Im1lIg=="} | {"agg_rev":"MjYz","membership":"InNpbHZlciI="} | {"age":"IjUxLTEwMCI=","gender":"IkZFTUFMRSI=","name":"IkphbWVzIEZvd2xlciI="} |
| [B@37c21afe | {"state":"Im1uIg=="} | {"agg_rev":"MjAy","membership":"InNpbHZlciI="} | {"age":"IjUxLTEwMCI=","gender":"Ik9USEVSIg==","name":"Ikd1aWxsZXJtbyBLb2VobGVyIg=="} |
+--------------+-----------------------+-------------------------------------------------+---------------------------------------------------------------------------------------+
5 rows selected再一次,这张表需要返回可读的数据类型的字节数据。
查询文件系统
随着查询用完整模式数据源(如hive)和部分模式(如MapR-DB HBase),钻提供了独特的功能直接在文件系统上执行SQL查询。可以是一个本地的文件系统，或者是分布式文件系统，如MapR-FS，HDFS，或者S3。
在drill环境下，一个文件或一个目录是和相关数据表含义相同的。因此,您可以直接在文件和目录执行SQL操作，不需要预先模式定义或者改变任何模型或模型管理。模式基于动态查询的发现。钻支持各种查询的文件格式,包括文本、CSV、Parquet,JSON。
在这个例子中,点击流数据来自JSON格式的移动/web应用程序。JSON文件有以下结构:
{"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"}}
{"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"}}
{"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工作区:
0: jdbc:drill:> use dfs.clicks; +-------+-----------------------------------------+ | ok | summary | +-------+-----------------------------------------+ | true | Default schema changed to [dfs.clicks] | +-------+-----------------------------------------+ 1 row selected
在这种情况下,设置工作区是一种简化写的查询机制。当你指定一个文件系统的工作空间,你可以缩短引用文件的查询。您可以在工作区中指定的位置提供一个相对路径,替代完整的文件路径。
例如:
"location":/mapr/demo.mapr.com/data/nested"
在这条路径下你想查询的任何文件或目录可以被与这条相关路径引用。点击目录相关下的查询，直接嵌套目录下面。
从clicks.json文件选择2行:
0: jdbc:drill:> select * from `clicks/clicks.json` limit 2;
+-----------+-------------+-----------+---------------------------------------------------+-------------------------------------------+
| trans_id | date | time | user_info | trans_info |
+-----------+-------------+-----------+---------------------------------------------------+-------------------------------------------+
| 31920 | 2014-04-26 | 12:17:12 | {"cust_id":22526,"device":"IOS5","state":"il"} | {"prod_id":[174,2],"purch_flag":"false"} |
| 31026 | 2014-04-20 | 13:50:29 | {"cust_id":16368,"device":"AOS4.2","state":"nc"} | {"prod_id":[],"purch_flag":"false"} |
+-----------+-------------+-----------+---------------------------------------------------+-------------------------------------------+
2 rows selectedFROM子句引用指向一个特定的文件。drill扩大在标准SQL FROM子句“相关表的”传统概念，在本地或者分布式文件系统引用一个文件。
唯一的特殊要求是使用back ticks去嵌套（围绕）文件路径。这是必要的,只要文件路径包含钻保留字或字符。
从campaign.json文件选择2行:
0: jdbc:drill:> select * from `clicks/clicks.campaign.json` limit 2;
+-----------+-------------+-----------+---------------------------------------------------+---------------------+----------------------------------------+
| trans_id | date | time | user_info | ad_info | trans_info |
+-----------+-------------+-----------+---------------------------------------------------+---------------------+----------------------------------------+
| 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"} |
| 31995 | 2014-05-22 | 16:06:38 | {"cust_id":17182,"device":"IOS6","state":"fl"} | {"camp_id":"null"} | {"prod_id":[],"purch_flag":"false"} |
+-----------+-------------+-----------+---------------------------------------------------+---------------------+----------------------------------------+
2 rows selected注意select * 查询，任何复杂的数据类型如地图和数组返回JSON字符串。您将看到如何使用不同的SQL函数和运算符解压这些数据，在下一课。
查询日志数据
和前面的示例不同,我们执行查询不在一个文件点击数据,日志数据被存储在文件系统分段的目录。 日志目录中有三个子目录:
2012年
2013年
2014年
每年这些目录fans出自一组月编号目录，每月目录包含那个月的一个JSON文件和日志记录。所有日志文件记录的总数是48000。
日志目录及其子目录中的文件都是JSON文件。有许多这些文件,但您可以使用drill他们所有作为一个单一数据源，或者去查询这些文件的子集。
设置dfs.logs工作区:
0: jdbc:drill:> use dfs.logs; +-------+---------------------------------------+ | ok | summary | +-------+---------------------------------------+ | true | Default schema changed to [dfs.logs] | +-------+---------------------------------------+ 1 row selected
从日志目录选择2行:
0: jdbc:drill:> select * from logs limit 2; +-------+-------+-----------+-------------+-----------+----------+---------+--------+----------+-----------+----------+-------------+ | dir0 | dir1 | trans_id | date | time | cust_id | device | state | camp_id | keywords | prod_id | purch_flag | +-------+-------+-----------+-------------+-----------+----------+---------+--------+----------+-----------+----------+-------------+ | 2012 | 8 | 109 | 08/07/2012 | 20:33:13 | 144618 | IOS5 | ga | 4 | hey | 6 | false | | 2012 | 8 | 119 | 08/19/2012 | 03:37:50 | 17 | IOS5 | tx | 16 | and | 50 | false | +-------+-------+-----------+-------------+-----------+----------+---------+--------+----------+-----------+----------+-------------+ 2 rows selected
请注意,这是平的JSON数据。dfs.clicks工作区位置属性指向一个包含日志目录的目录，让FROM子句参考这个查询非常简单。你不需要参考系统上的完整文件目录路径。
列名dir0 dir1是特殊的drill变量，确定在日志目录下的子目录。在第三课,你做更复杂的查询,利用这些动态变量。
在日志目录中找到的行总数(所有文件):
0: jdbc:drill:> select count(*) from logs; +---------+ | EXPR$0 | +---------+ | 48000 | +---------+ 1 row selected
这个查询遍历所有的日志目录和其子目录文件返回这些文件的总行数。
Lesson2:用ANSI SQL运行查询→