[jira] [Commented] (DRILL-3180) Apache Drill JDBC storage plugin to query rdbms systems such as MySQL and Netezza from Apache Drill

[Magnus Pierre (JIRA)]

    [ 
https://issues.apache.org/jira/browse/DRILL-3180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14681863#comment-14681863
 ] 

Magnus Pierre commented on DRILL-3180:
--------------------------------------

Proposed changes to get the new codeline to run.

Add cast to SchemaUtilities.java
public static AbstractSchema unwrapAsDrillSchemaInstance(SchemaPlus schemaPlus) 
 {
    try {
      return (AbstractSchema) 
schemaPlus.unwrap(AbstractSchema.class).getDefaultSchema();
    } catch (ClassCastException e) {
      throw UserException.validationError(e)
          .message("Schema [%s] is not a Drill schema.", 
getSchemaPath(schemaPlus))
          .build(logger);
    }
  }
—————

Add:  source.setPassword(config.getPassword()); to JdbcStoragePlugin after 
setUsername
Add:

  @Override
      public Table getTable(String name) {
        List<String> lst = new ArrayList<String>();
        if(name.contains(".")) {
          String[] val = name.split("\\.");
          JdbcSchema sub = (JdbcSchema) this.schemaMap.get(val[0]);
          return sub.getTable(val[1]);
        }
      
        try {
            DatabaseMetaData meta = source.getConnection().getMetaData();
            ResultSet rs =meta.getTables(null, null, name,null);
            while (rs.next()) {
              lst.add(rs.getString("TABLE_NAME"));
            }
        } catch (SQLException e) {
            throw new DrillRuntimeException(e);
        }
        return new DynamicDrillTable(JdbcStoragePlugin.this, 
JdbcStoragePlugin.this.getName(),lst.get(0));

      }

to JdbcCatalogSchema, listing tables does not work though.

Also:

Re: not getting the rules to push down... Might be due to statistics…

I figured the other week how to get reliable statistics from the db without too 
much hassle: (using cardinality from indexes)


public ScanStats getScanStats(String database, String tableName) {
        long largestVal = 0;
        ScanStats scanS = scanStats.get(database + "." + tableName);
        if(scanS == null) {
        try {
            if(largestVal == 0) {
               ResultSet result = 
this.getBasicDataSource().getConnection().getMetaData().getIndexInfo(database,null,
 tableName, false, true);

               while(result.next()) {
                   long value = result.getLong("CARDINALITY");
                   largestVal = value > largestVal ? value : largestVal;
               }
               result.close();
           }
       } catch (SQLException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }

        largestVal = largestVal > 0 ? largestVal : 100;
        scanStats.put(database +"." + tableName, new 
ScanStats(GroupScanProperty.NO_EXACT_ROW_COUNT,largestVal,20,20));
        return scanStats.get(database + "." + tableName);
      }

      return scanS;
    }

Need to be adjusted to fit the new code line.


> Apache Drill JDBC storage plugin to query rdbms systems such as MySQL and 
> Netezza from Apache Drill
> ---------------------------------------------------------------------------------------------------
>
>                 Key: DRILL-3180
>                 URL: https://issues.apache.org/jira/browse/DRILL-3180
>             Project: Apache Drill
>          Issue Type: New Feature
>          Components: Storage - Other
>    Affects Versions: 1.0.0
>            Reporter: Magnus Pierre
>            Assignee: Jacques Nadeau
>              Labels: Drill, JDBC, plugin
>             Fix For: 1.2.0
>
>         Attachments: pom.xml, storage-mpjdbc.zip
>
>   Original Estimate: 1m
>  Remaining Estimate: 1m
>
> I have developed the base code for a JDBC storage-plugin for Apache Drill. 
> The code is primitive but consitutes a good starting point for further 
> coding. Today it provides primitive support for SELECT against RDBMS with 
> JDBC. 
> The goal is to provide complete SELECT support against RDBMS with push down 
> capabilities.
> Currently the code is using standard JDBC classes.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)