Drill查询复杂的数据结构

Posted on By 司马顿

我发现Drill查询复杂的数据结构（map, array）等，还是挺麻烦的，要做多个临时表的join。

比如这个Json:

{     
     "first_name":
     {
       "0":"Robert",
       "1":"Steve",
       "2":"Anne",
       "3":"Alice"
     },
      "last_name":
      {
        "0":"Hernandez",
        "1":"Smith",
        "2":"Raps",
        "3":"Muller"
     },
      "birthday":
      {
        "0":"5/3/67",
        "1":"8/4/84",
        "2":"9/13/91",
        "3":"4/15/75"
      } 
}

要按照合理的表格布局来查询，得写如下一大堆SQL：

> select firstName,lastName,birthday from
> (
>   select row_number() over(order by '1') as rowID,
>   flatten(kvgen(first_name))['value'] as firstName
>   from `columns.json`
> ) as t1 join 
> (
>   select row_number() over(order by '1') as rowID,
>   flatten(kvgen(last_name))['value'] as lastName
>   from `columns.json`
> ) as t2 on t1.rowID = t2.rowID
> join
> ( 
>   select row_number() over(order by '1') as rowID,
>   flatten(kvgen(birthday))['value'] as birthday
>   from `columns.json`
> ) as t3 on t1.rowID = t3.rowID;

+-----------+-----------+----------+
| firstName | lastName  | birthday |
+-----------+-----------+----------+
| Robert    | Hernandez | 5/3/67   |
| Steve     | Smith     | 8/4/84   |
| Anne      | Raps      | 9/13/91  |
| Alice     | Muller    | 4/15/75  |
+-----------+-----------+----------+
4 rows selected (0.368 seconds)

主要在于drill读取Json默认还是压缩到一起的，要调用kvgen和flatten之类的函数，把它们由一行多列的格式，打散成多行一列的格式。

比如默认的select是这种格式：

> select first_name from `columns.json`;
+---------------------------------------------------+
|                    first_name                     |
+---------------------------------------------------+
| {"0":"Robert","1":"Steve","2":"Anne","3":"Alice"} |
+---------------------------------------------------+
1 row selected (0.141 seconds)

调用kvgen（产生map）和flatten（打散）函数后，才能得到这种格式：

> select flatten(kvgen(first_name)) from `columns.json`;
+------------------------------+
|            EXPR$0            |
+------------------------------+
| {"key":"0","value":"Robert"} |
| {"key":"1","value":"Steve"}  |
| {"key":"2","value":"Anne"}   |
| {"key":"3","value":"Alice"}  |
+------------------------------+
4 rows selected (0.14 seconds)

当然可以单独3个select得到各个列的数据：

> select row_number() over (order by '1') as rowID, flatten(kvgen(first_name))['value'] as firstName from `columns.json`;
+-------+-----------+
| rowID | firstName |
+-------+-----------+
| 1     | Robert    |
| 2     | Steve     |
| 3     | Anne      |
| 4     | Alice     |
+-------+-----------+
4 rows selected (0.176 seconds)

> select row_number() over (order by '1') as rowID, flatten(kvgen(last_name))['value'] as lastName from `columns.json`;
+-------+-----------+
| rowID | lastName  |
+-------+-----------+
| 1     | Hernandez |
| 2     | Smith     |
| 3     | Raps      |
| 4     | Muller    |
+-------+-----------+
4 rows selected (0.155 seconds)

> select row_number() over (order by '1') as rowID, flatten(kvgen(birthday))['value'] as birthday from `columns.json`;
+-------+----------+
| rowID | birthday |
+-------+----------+
| 1     | 5/3/67   |
| 2     | 8/4/84   |
| 3     | 9/13/91  |
| 4     | 4/15/75  |
+-------+----------+
4 rows selected (0.124 seconds)

但要把它们join到一起，就要按照第一个SQL代码去执行了。这样的SQL写起来很长，也比较费力。不过相比起来，drill能把Json按照SQL语句去解析，已经很强大了。这样对无处不在的Json数据的查询，就有了趁手的工具。

Print this entry

博客