JAQL is a JSON query language similar to SQL. One key difference is the dataset is accessed more like objects. A great overview is found here:JaqlOverview the strength of JAQL is it allows users simple, extendable code to manipulate data that is in a non-proprietary, readable, and commonly used file format. It is primarily used when processing data with Hadoop. JAQL tends to crash when it has exceptions so I would copy-paste my command from an editor. Also never press the up arrow.
Example Data: |
|
Customer |
CustomerID |
Name |
Age |
1 |
Joe |
23 |
2 |
Mika |
45 |
3 |
Lin |
34 |
4 |
Sara |
56 |
5 |
Susan |
18 |
|
PurchaseOrder |
POID |
CustomerID |
Purchase |
1 |
3 |
Fiction |
2 |
1 |
Biography |
3 |
1 |
Fiction |
4 |
2 |
Biography |
5 |
3 |
Fiction |
6 |
4 |
Fiction |
|
Create Data
$Customer = [ {CustomerId:1, name: Joe , age: 23}
, {CustomerId:1, name: Mika , age: 45}
, {CustomerId:1, name: Lin , age: 34}
, {CustomerId:1, name: Sara , age: 56}
, {CustomerId:1, name: Susan , age: 18}];
$PurchaseOrder = [
{POID: 1, CustomerId:3, Purchase: Fiction }
{POID: 2, CustomerId:1, Purchase: Biography }
{POID: 3, CustomerId:1, Purchase: Fiction }
{POID: 4, CustomerId:2, Purchase: Biography }
{POID: 5, CustomerId:3, Purchase: Fiction }
{POID: 6, CustomerId:3, Purchase: Fiction } ];
SELECT
hdfs($Customers) ;
hdfs($PurchaseOrder) ;
CustomerID |
Name |
Age |
1 |
Joe |
23 |
2 |
Mika |
45 |
3 |
Lin |
34 |
4 |
Sara |
56 |
5 |
Susan |
18 |
Sort BY
$Customers -> sort by [$.age];
CustomerID |
Name |
Age |
5 |
Susan |
18 |
1 |
Joe |
23 |
3 |
Lin |
34 |
2 |
Mika |
45 |
4 |
Sara |
56 |
Filter
$Customers -> filter $.age == 18 ;
CustomerID |
Name |
Age |
5 |
Susan |
18 |
INNER JOIN
join $Customers, $PurchaseOrder where $Customers.CustomerId == $PurchaseOrder.CustomerId into {$Customers.name, $PurchaseOrder.*} ;
CustomerID |
Name |
Age |
POID |
Purchase |
1 |
Joe |
23 |
2 |
Biography |
1 |
Joe |
23 |
3 |
Fiction |
2 |
Mika |
45 |
4 |
Biography |
3 |
Lin |
34 |
1 |
Fiction |
3 |
Lin |
34 |
5 |
Fiction |
4 |
Sara |
56 |
6 |
Fiction |
LEFT OUTER JOIN
join preserve $Customers, $PurchaseOrder where $Customers.CustomerId == $PurchaseOrder.CustomerId into {$Customers.name, $PurchaseOrder.*} ;
CustomerID |
Name |
Age |
POID |
Purchase |
1 |
Joe |
23 |
2 |
Biography |
1 |
Joe |
23 |
3 |
Fiction |
2 |
Mika |
45 |
4 |
Biography |
3 |
Lin |
34 |
1 |
Fiction |
3 |
Lin |
34 |
5 |
Fiction |
4 |
Sara |
56 |
6 |
Fiction |
5 |
Susan |
18 |
NULL |
NULL |
GROUP BY
$Customers -> group by $Customers_group = $.CustomerID into {$Customers_group, $Customers.age, total: count($) };
Name |
Age |
Orders |
Joe |
23 |
1 |
Mika |
45 |
2 |
Lin |
34 |
2 |
Sara |
56 |
1 |
UPDATE
$Customer1= $Customers -> filter $.CustomerId == 1 -> transform {CustomerId: $.CustomerId, name: $.name ,age: 16} ; $Customerne1= $Customers -> filter $.CustomerId != 1 ; hdfs($Customer1 , $Customerne1);
Customer |
CustomerID |
Name |
Age |
1 |
Joe |
26 |
2 |
Mika |
45 |
3 |
Lin |
34 |
4 |
Sara |
56 |
5 |
Susan |
18 |
INSERT
$Customer6=[{CustomerId:6,name: Terry ,age:50}]; hdfs($Customers , $Customer6); -> write(file( Customers.dat ));
Customer |
CustomerID |
Name |
Age |
1 |
Joe |
23 |
2 |
Mika |
45 |
3 |
Lin |
34 |
4 |
Sara |
56 |
5 |
Susan |
18 |
6 |
Terry |
50 |
DELETE
$Customerne1= $Customers ->filter $.CustomerId != 1
Customer |
CustomerID |
Name |
Age |
2 |
Mika |
45 |
3 |
Lin |
34 |
4 |
Sara |
56 |
5 |
Susan |
18 |