AnsweredAssumed Answered

Apache Drill: Create Table Error While Selecting from Json Data

Question asked by vikasreddya on Mar 23, 2017
Latest reply on Apr 4, 2017 by maprcommunity

Below command is working fine.

SELECT TO_TIMESTAMP(`ts`,'yyyyMMddHHmmss') FROM dfs.tmp`/mapr/my.cluster.com/hive/cpf_sales.json

 

But when I am trying to create a table from select statement then it is giving an error.    Below are the one's I tried

=================================================================================

ALTER SESSION SET `store.format`='json';
use dfs;
CREATE TABLE by_yr (gen_date)  AS SELECT TO_TIMESTAMP(`ts`,'yyyyMMddHHmmss') FROM dfs.`/mapr/my.cluster.com/hive/cpf_sales.json` LIMIT 100;

 

Error:

org.apache.drill.common.exceptions.UserRemoteException: PARSE ERROR: Encountered ";" at line 1, column 8. Was expecting one of: "." ... "[" ... SQL Query use dfs; ^ CREATE TABLE by_yr (gen_date) AS SELECT TO_TIMESTAMP(`ts`,'yyyyMMddHHmmss') FROM dfs.`/mapr/my.cluster.com/hive/cpf_sales.json` LIMIT 100 [Error Id: 81cbe394-b3c6-4c34-80ad-83325f748ae1 on iot3:31010]

 

use dfs.tmp;
CREATE TABLE by_yr (gen_date)  AS SELECT TO_TIMESTAMP(`ts`,'yyyyMMddHHmmss') FROM dfs.tmp`/mapr/my.cluster.com/hive/cpf_sales.json` LIMIT 100;

 

Error:

org.apache.drill.common.exceptions.UserRemoteException: PARSE ERROR: Encountered ";" at line 1, column 12. Was expecting one of: "." ... "[" ... SQL Query use dfs.tmp; ^ SELECT COLUMNS[0], COLUMNS[1] from dfs.tmp.`/mapr/my.cluster.com/donuts.json` [Error Id: 5e9d1d20-a804-4d09-8b69-d76b3c009647 on iot2:31010]

Outcomes