Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Drill >> mail # dev >> Re: [jira] [Commented] (DRILL-159) 1.sql


Copy link to this message
-
Re: [jira] [Commented] (DRILL-159) 1.sql
You mean this, Julian.

    private static final String MODEL_LINEITEM           "{\n"
        + "  version: '1.0',\n"
        + "   schemas: [\n"
        + "     {\n"
        + "       name: 'LINEITEM',\n"
        + "       tables: [\n"
        + "         {\n"
        + "           name: 'LINEITEM',\n"
        + "           type: 'custom',\n"
        + "           factory: '" + DrillTable.Factory.class.getName() + "',\n"
        + "           operand: {\n"
        + "             path: '/lineitem.tbl.1.json'\n"
        + "           }\n"
        + "         }\n"
        + "       ]\n"
        + "     }\n"
        + "   ]\n"
        + "}";
V
________________________________
 From: Julian Hyde <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Cc: Sree V <[EMAIL PROTECTED]>
Sent: Wednesday, July 31, 2013 12:31 AM
Subject: Re: [jira] [Commented] (DRILL-159) 1.sql
 

Ah, I just remembered that we don't have a schema! Therefore the table just has one column, called _MAP. (Run "!columns" and you'll find out.)

I usually create an Optiq schema with a table and a view for each "table". The view creates columns on the fly using the [...] and CAST operators, then assigns them names using AS.

For example,

{
  version: '1.0',
   schemas: [
     {
       name: 'HR',
       tables: [
         {
           name: 'EMPLOYEES',
           type: 'custom',
           factory: 'org.apache.drill.jdbc.DrillTable.Factory'
         },
         {
           name: 'DEPARTMENTS',
           type: 'custom',
           factory: 'org.apache.drill.jdbc.DrillTable.Factory'
         },
         {
           name: 'EMP',
           type: 'view',
           sql: 'select _MAP[\\'deptId\\'] as deptid, cast(_MAP[\\'lastName\\'] as varchar) as lastName from employees'
         },
         {
           name: 'DEPT',
           type: 'view',
           sql: 'select _MAP[\\'deptId\\'] as deptid, _MAP[\\'name\\'] as name from departments'
         }
       ]
     }
   ]
}
If there's a more elegant way to provide schema, someone please holler!

Julian
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB