Hue SQL tricks

I had to do the following to get a create table to run. Be aware that it will complain about everything without much of a clue as to the error.

1) I had to be in the area the source tables were in

2) Removed hive from all table references

 create table hive.ltobias.xyz

3) Removed the formatting from the to_date:

> to_date('2017-01-01', 'yyyy-MM-dd')

> to_date('2017-01-01')

4) Put backticks around items as alias (apparently, it's a keyword)

 join db1.items  `items` 

5) Remember to hit refresh to see the table. 

HUE: Create Database

Click the refresh button above the drop down list if your database doesn't automatically display.

MapR 5.0 Documentation / Drill Tutorial - Lesson 1: Learn About the Data Set

Lesson 1:

MapR Drill - Lesson 1


drill:> show databases;
------------------------------------------------------------------

Enable the DECIMAL Data Type 

This tutorial uses the DECIMAL data type in some examples. The DECIMAL data type is disabled by default in this release, so enable the DECIMAL data type before proceeding: 

alter session set `planner.enable_decimal_data_type`=true; ------------------------------------------------------------------ describe hive.nextgen_rjr.transaction_line_items

------------------------------------------------------------------

Lesson 2:

MapR Drill - Lesson 2


select cast(row_key as int)
, regexp_replace(cast(t.address.state as varchar(10)),'"','')

from customers t limit 1;

------------------------------------------------------------------


A mutable (or writable) workspace is a workspace that is enabled for “write” operations. This attribute is part of the storage plugin configuration. You can create Drill views and tables in mutable workspaces.
-----------------------------------------------------------------


create or replace view custview as select cast(row_key as int) as cust_id,

cast(t.personal.name as varchar(20)) as name, 

cast(t.personal.gender as varchar(10)) as gender, 

cast(t.personal.age as varchar(10)) as age, 

cast(t.address.state as varchar(4)) as state,

cast(t.loyalty.agg_rev as dec(7,2)) as agg_rev,

cast(t.loyalty.membership as varchar(20)) as membership

from maprdb.customers t;

A view is simply a special file with a specific extension (.drill). You can store views even in your local file system or point to a specific workspace
------------------------------------------------------------------


!set maxwidth 10000  -- can't get to work


------------------------------------------------------------------

Repeated Count

https://drill.apache.org/docs/repeated-count/

This function counts the values in an array.

Syntax ¶

REPEATED_COUNT (array)

array is the name of an array.

Usage Notes ¶

The COUNT function requires a single argument, which must be an array. Note that this function is not a standard SQL aggregate function and does not require the count to be grouped by other columns in the select list (such as name in this example).

Example ¶

The following example returns the counts for the categories array in the yelp_academic_dataset_business.json file. The counts are restricted to rows that contain the string pizza.

SELECT name, REPEATED_COUNT(categories) 
FROM   dfs.yelp.`yelp_academic_dataset_business.json` 
WHERE  name LIKE '%pizza%';

+---------------+------------+
|    name       |   EXPR$1   |
+---------------+------------+
| Villapizza    | 2          |
| zpizza        | 4          |
| zpizza        | 4          |
| Luckys pizza  | 2          |
| Zpizza        | 2          |
| S2pizzabar    | 4          |
| Dominos pizza | 5          |
+---------------+------------+

7 rows selected (2.03 seconds)