Push down not work expected

I have added a custom data source that name is maxcpt_myproject.

java.sql.SQLException: Fail to run sql:select count(*) from `myproject`.`myproject`.`tab1`;, Error:[400] com.aliyun.odps.OdpsException: ODPS-0130161:[1,47] Parse exception - invalid token '.'

The correct sql should be select count(*) from myproject . tab1``, but pushed down a wrong sql with duplicate projects.

@koolay, the Dremio query window has a “SQL context” feature that expands the dataset names to their full path. If you remove the context, are you able to run the query?

Screen Shot 2020-07-27 at 9.58.48 AM

@ben

I have already removed the context, it still not work with the same error.

Can you explain in more detail what your “custom data source” is?

@ben

The data source is on aliyun-odps-jdbc.

My arp.yaml file:

metadata:
  # Manually Configured Metadata Section.
  name: MAXCOMPUTE
  apiname: maxcompute
  spec_version: '1'

syntax:
  # Manually Configured Syntax Section.
  identifier_quote: '`'
  identifier_length_limit: 128
  allows_boolean_literal: false
  map_boolean_literal_to_bit: false
  supports_catalogs: false
  supports_schemas: false
  ...
  ...

MaxcomputeConf.java file:

@SourceType(value = "MAXCOMPUTE", label = "Maxcompute")
public class MaxcomputeConf extends AbstractArpConf<MaxcomputeConf> {
  private static final String ARP_FILENAME = "arp/implementation/maxcompute-arp.yaml";
  private static final ArpDialect ARP_DIALECT =
      AbstractArpConf.loadArpFile(ARP_FILENAME, (ArpDialect::new));
  private static final String DRIVER = "com.aliyun.odps.jdbc.OdpsDriver";

  @NotBlank
  @Tag(1)
  @DisplayMetadata(label = "JDBC URL (Ex: jdbc:odps:http://service.cn.maxcompute.aliyun.com/api?project=xxx&charset=utf-8&access_id=xxx&access_key=xxx)")
  public String jdbcURL;

  @Tag(2)
  @DisplayMetadata(label = "Record fetch size")
  @NotMetadataImpacting
  public int fetchSize = 200;

  @VisibleForTesting
  public String toJdbcConnectionString() {
    checkNotNull(this.jdbcURL, "Missing jdbcURL.");
    return jdbcURL;
  }

  @Override
  @VisibleForTesting
  public Config toPluginConfig(SabotContext context) {
    return JdbcStoragePlugin.Config.newBuilder()
        .withDialect(getDialect())
        .withFetchSize(fetchSize)
        .withDatasourceFactory(this::newDataSource)
        .clearHiddenSchemas()
        .addHiddenSchema("SYSTEM")
        .build();
  }

  private CloseableDataSource newDataSource() {
    return DataSources.newGenericConnectionPoolDataSource(DRIVER,
      toJdbcConnectionString(), null, null, null, DataSources.CommitMode.DRIVER_SPECIFIED_COMMIT_MODE);
  }

  @Override
  public ArpDialect getDialect() {
    return ARP_DIALECT;
  }

  @VisibleForTesting
  public static ArpDialect getDialectSingleton() {
    return ARP_DIALECT;
  }
}

Would you be able to attach a profile for the failing query?

@jduong

profile:

ddfdbcaa-bebf-4374-afeb-7b28013a0fcf.zip (6.4 KB)

dremio logs:

2020-07-29 07:05:29,173 [20dee04a-1aba-5e11-5a89-c13206520200/0:foreman-planning] WARN  c.d.e.store.jdbc.JdbcSchemaFetcher - Took longer than 5 seconds to execute query `select count(*) from `mypaas_dmp`.`mypaas_dmp`.`aa``.
java.sql.SQLException: Fail to run sql:select count(*) from `mypaas_dmp`.`mypaas_dmp`.`aa`;, Error:[400] com.aliyun.odps.OdpsException: ODPS-0130161:[1,47] Parse exception - invalid token '.'
	at com.aliyun.odps.jdbc.OdpsStatement.runSQL(OdpsStatement.java:732)
	at com.aliyun.odps.jdbc.OdpsStatement.executeQuery(OdpsStatement.java:206)
	at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:206)
	at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:206)
	at com.dremio.exec.store.jdbc.JdbcSchemaFetcher.executeQueryAndGetFirstLong(JdbcSchemaFetcher.java:444)
	at com.dremio.exec.store.jdbc.JdbcSchemaFetcher.getRowCount(JdbcSchemaFetcher.java:387)
	at com.dremio.exec.store.jdbc.JdbcDatasetMetadata.iterator(JdbcDatasetMetadata.java:89)
	at com.dremio.exec.catalog.DatasetSaver.save(DatasetSaver.java:98)
	at com.dremio.exec.catalog.DatasetSaver.save(DatasetSaver.java:154)

This particular exception is benign. It’s for calculating the rowcount which is used for planning (for example, when joining two tables the row count of the two is factored in to determine what is the better order for the join).

You can make this work as well by extending ArpSchemaFetcher and overriding getRowCount() and adding your own method to get the row count, then changing your ArpDialect implementation to return your JdbcSchemaFetcher.

1 Like

@jduong
@ben

Thanks for your reply.
Where I can find the example about that how to override JdbcSchemaFetcher ?