Search in sources :

Example 16 with SparkSqlDialect

use of org.apache.calcite.sql.dialect.SparkSqlDialect in project hopsworks by logicalclocks.

the class TestConstructorController method testSingleJoinOrderBySQLQueryOnline.

@Test
public void testSingleJoinOrderBySQLQueryOnline() throws Exception {
    List<Feature> availableLeft = new ArrayList<>();
    availableLeft.add(new Feature("ft1", "fg1", "Float", null, null));
    List<Feature> availableRight = new ArrayList<>();
    availableRight.add(new Feature("ft1", "fg2", "Float", null, null));
    Query leftQuery = new Query("fs1", "project_fs2", fg1, "fg1", availableLeft, availableLeft);
    Query rightQuery = new Query("fs1", "project_fs1", fg2, "fg2", availableRight, availableRight);
    Join join = new Join(leftQuery, rightQuery, availableLeft, availableLeft, JoinType.INNER, null, singleEqualsJoinOperator);
    leftQuery.setJoins(Arrays.asList(join));
    List<Feature> orderByList = new ArrayList<>();
    orderByList.addAll(availableLeft);
    orderByList.addAll(availableRight);
    leftQuery.setOrderByFeatures(orderByList);
    String query = target.generateSQL(leftQuery, true).toSqlString(new SparkSqlDialect(SqlDialect.EMPTY_CONTEXT)).getSql().replace("\n", " ");
    Assert.assertEquals("SELECT `fg1`.`ft1`, `fg2`.`ft1` FROM `project_fs2`.`fg1_1` `fg1` INNER JOIN " + "`project_fs1`.`fg2_1` `fg2` ON `fg1`.`ft1` = `fg2`.`ft1` ORDER BY `fg1`.`ft1`, `fg2`.`ft1`", query);
}
Also used : SparkSqlDialect(org.apache.calcite.sql.dialect.SparkSqlDialect) ArrayList(java.util.ArrayList) Join(io.hops.hopsworks.common.featurestore.query.join.Join) Test(org.junit.Test)

Example 17 with SparkSqlDialect

use of org.apache.calcite.sql.dialect.SparkSqlDialect in project calcite by apache.

the class SqlDialectFactoryImpl method create.

@Override
public SqlDialect create(DatabaseMetaData databaseMetaData) {
    SqlDialect.Context c = SqlDialects.createContext(databaseMetaData);
    String databaseProductName = c.databaseProductName();
    try {
        if (databaseProductName == null) {
            databaseProductName = databaseMetaData.getDatabaseProductName();
        }
    } catch (SQLException e) {
        throw new RuntimeException("while detecting database product", e);
    }
    final String upperProductName = databaseProductName.toUpperCase(Locale.ROOT).trim();
    switch(upperProductName) {
        case "ACCESS":
            return new AccessSqlDialect(c);
        case "APACHE DERBY":
            return new DerbySqlDialect(c);
        case "CLICKHOUSE":
            return new ClickHouseSqlDialect(c);
        case "DBMS:CLOUDSCAPE":
            return new DerbySqlDialect(c);
        case "EXASOL":
            return new ExasolSqlDialect(c);
        case "HIVE":
            return new HiveSqlDialect(c);
        case "INGRES":
            return new IngresSqlDialect(c);
        case "INTERBASE":
            return new InterbaseSqlDialect(c);
        case "JETHRODATA":
            return new JethroDataSqlDialect(c.withJethroInfo(jethroCache.get(databaseMetaData)));
        case "LUCIDDB":
            return new LucidDbSqlDialect(c);
        case "ORACLE":
            return new OracleSqlDialect(c);
        case "PHOENIX":
            return new PhoenixSqlDialect(c);
        case "MYSQL (INFOBRIGHT)":
            return new InfobrightSqlDialect(c);
        case "MYSQL":
            return new MysqlSqlDialect(c.withDataTypeSystem(MysqlSqlDialect.MYSQL_TYPE_SYSTEM));
        case "REDSHIFT":
            return new RedshiftSqlDialect(c.withDataTypeSystem(RedshiftSqlDialect.TYPE_SYSTEM));
        case "SNOWFLAKE":
            return new SnowflakeSqlDialect(c);
        case "SPARK":
            return new SparkSqlDialect(c);
        default:
            break;
    }
    // Now the fuzzy matches.
    if (databaseProductName.startsWith("DB2")) {
        return new Db2SqlDialect(c);
    } else if (upperProductName.contains("FIREBIRD")) {
        return new FirebirdSqlDialect(c);
    } else if (databaseProductName.startsWith("Informix")) {
        return new InformixSqlDialect(c);
    } else if (upperProductName.contains("NETEZZA")) {
        return new NetezzaSqlDialect(c);
    } else if (upperProductName.contains("PARACCEL")) {
        return new ParaccelSqlDialect(c);
    } else if (databaseProductName.startsWith("HP Neoview")) {
        return new NeoviewSqlDialect(c);
    } else if (upperProductName.contains("POSTGRE")) {
        return new PostgresqlSqlDialect(c.withDataTypeSystem(PostgresqlSqlDialect.POSTGRESQL_TYPE_SYSTEM));
    } else if (upperProductName.contains("SQL SERVER")) {
        return new MssqlSqlDialect(c);
    } else if (upperProductName.contains("SYBASE")) {
        return new SybaseSqlDialect(c);
    } else if (upperProductName.contains("TERADATA")) {
        return new TeradataSqlDialect(c);
    } else if (upperProductName.contains("HSQL")) {
        return new HsqldbSqlDialect(c);
    } else if (upperProductName.contains("H2")) {
        return new H2SqlDialect(c);
    } else if (upperProductName.contains("VERTICA")) {
        return new VerticaSqlDialect(c);
    } else if (upperProductName.contains("SNOWFLAKE")) {
        return new SnowflakeSqlDialect(c);
    } else if (upperProductName.contains("SPARK")) {
        return new SparkSqlDialect(c);
    } else {
        return new AnsiSqlDialect(c);
    }
}
Also used : H2SqlDialect(org.apache.calcite.sql.dialect.H2SqlDialect) SQLException(java.sql.SQLException) NeoviewSqlDialect(org.apache.calcite.sql.dialect.NeoviewSqlDialect) InformixSqlDialect(org.apache.calcite.sql.dialect.InformixSqlDialect) ExasolSqlDialect(org.apache.calcite.sql.dialect.ExasolSqlDialect) TeradataSqlDialect(org.apache.calcite.sql.dialect.TeradataSqlDialect) HsqldbSqlDialect(org.apache.calcite.sql.dialect.HsqldbSqlDialect) OracleSqlDialect(org.apache.calcite.sql.dialect.OracleSqlDialect) InterbaseSqlDialect(org.apache.calcite.sql.dialect.InterbaseSqlDialect) ClickHouseSqlDialect(org.apache.calcite.sql.dialect.ClickHouseSqlDialect) AccessSqlDialect(org.apache.calcite.sql.dialect.AccessSqlDialect) SybaseSqlDialect(org.apache.calcite.sql.dialect.SybaseSqlDialect) LucidDbSqlDialect(org.apache.calcite.sql.dialect.LucidDbSqlDialect) IngresSqlDialect(org.apache.calcite.sql.dialect.IngresSqlDialect) JethroDataSqlDialect(org.apache.calcite.sql.dialect.JethroDataSqlDialect) AnsiSqlDialect(org.apache.calcite.sql.dialect.AnsiSqlDialect) MssqlSqlDialect(org.apache.calcite.sql.dialect.MssqlSqlDialect) RedshiftSqlDialect(org.apache.calcite.sql.dialect.RedshiftSqlDialect) VerticaSqlDialect(org.apache.calcite.sql.dialect.VerticaSqlDialect) FirebirdSqlDialect(org.apache.calcite.sql.dialect.FirebirdSqlDialect) SnowflakeSqlDialect(org.apache.calcite.sql.dialect.SnowflakeSqlDialect) InfobrightSqlDialect(org.apache.calcite.sql.dialect.InfobrightSqlDialect) MysqlSqlDialect(org.apache.calcite.sql.dialect.MysqlSqlDialect) NetezzaSqlDialect(org.apache.calcite.sql.dialect.NetezzaSqlDialect) SparkSqlDialect(org.apache.calcite.sql.dialect.SparkSqlDialect) ParaccelSqlDialect(org.apache.calcite.sql.dialect.ParaccelSqlDialect) PostgresqlSqlDialect(org.apache.calcite.sql.dialect.PostgresqlSqlDialect) HiveSqlDialect(org.apache.calcite.sql.dialect.HiveSqlDialect) Db2SqlDialect(org.apache.calcite.sql.dialect.Db2SqlDialect) PrestoSqlDialect(org.apache.calcite.sql.dialect.PrestoSqlDialect) AnsiSqlDialect(org.apache.calcite.sql.dialect.AnsiSqlDialect) H2SqlDialect(org.apache.calcite.sql.dialect.H2SqlDialect) ClickHouseSqlDialect(org.apache.calcite.sql.dialect.ClickHouseSqlDialect) DerbySqlDialect(org.apache.calcite.sql.dialect.DerbySqlDialect) SybaseSqlDialect(org.apache.calcite.sql.dialect.SybaseSqlDialect) TeradataSqlDialect(org.apache.calcite.sql.dialect.TeradataSqlDialect) OracleSqlDialect(org.apache.calcite.sql.dialect.OracleSqlDialect) AccessSqlDialect(org.apache.calcite.sql.dialect.AccessSqlDialect) VerticaSqlDialect(org.apache.calcite.sql.dialect.VerticaSqlDialect) CalciteSqlDialect(org.apache.calcite.sql.dialect.CalciteSqlDialect) SparkSqlDialect(org.apache.calcite.sql.dialect.SparkSqlDialect) PhoenixSqlDialect(org.apache.calcite.sql.dialect.PhoenixSqlDialect) SnowflakeSqlDialect(org.apache.calcite.sql.dialect.SnowflakeSqlDialect) ExasolSqlDialect(org.apache.calcite.sql.dialect.ExasolSqlDialect) HsqldbSqlDialect(org.apache.calcite.sql.dialect.HsqldbSqlDialect) IngresSqlDialect(org.apache.calcite.sql.dialect.IngresSqlDialect) ParaccelSqlDialect(org.apache.calcite.sql.dialect.ParaccelSqlDialect) InterbaseSqlDialect(org.apache.calcite.sql.dialect.InterbaseSqlDialect) MssqlSqlDialect(org.apache.calcite.sql.dialect.MssqlSqlDialect) NetezzaSqlDialect(org.apache.calcite.sql.dialect.NetezzaSqlDialect) InfobrightSqlDialect(org.apache.calcite.sql.dialect.InfobrightSqlDialect) PostgresqlSqlDialect(org.apache.calcite.sql.dialect.PostgresqlSqlDialect) RedshiftSqlDialect(org.apache.calcite.sql.dialect.RedshiftSqlDialect) FirebirdSqlDialect(org.apache.calcite.sql.dialect.FirebirdSqlDialect) MysqlSqlDialect(org.apache.calcite.sql.dialect.MysqlSqlDialect) Db2SqlDialect(org.apache.calcite.sql.dialect.Db2SqlDialect) JethroDataSqlDialect(org.apache.calcite.sql.dialect.JethroDataSqlDialect) NeoviewSqlDialect(org.apache.calcite.sql.dialect.NeoviewSqlDialect) BigQuerySqlDialect(org.apache.calcite.sql.dialect.BigQuerySqlDialect) HiveSqlDialect(org.apache.calcite.sql.dialect.HiveSqlDialect) InformixSqlDialect(org.apache.calcite.sql.dialect.InformixSqlDialect) LucidDbSqlDialect(org.apache.calcite.sql.dialect.LucidDbSqlDialect) DerbySqlDialect(org.apache.calcite.sql.dialect.DerbySqlDialect) PhoenixSqlDialect(org.apache.calcite.sql.dialect.PhoenixSqlDialect)

Example 18 with SparkSqlDialect

use of org.apache.calcite.sql.dialect.SparkSqlDialect in project hopsworks by logicalclocks.

the class ConstructorController method construct.

public FsQueryDTO construct(Query query, boolean pitEnabled, boolean isTrainingDataset, Project project, Users user) throws FeaturestoreException, ServiceException {
    FsQueryDTO fsQueryDTO = new FsQueryDTO();
    fsQueryDTO.setQuery(makeOfflineQuery(query));
    fsQueryDTO.setHudiCachedFeatureGroups(getHudiAliases(query, new ArrayList<>(), project, user));
    fsQueryDTO.setOnDemandFeatureGroups(getOnDemandAliases(user, project, query, new ArrayList<>()));
    // if on-demand feature groups are involved in the query, we don't support online queries
    if (fsQueryDTO.getOnDemandFeatureGroups().isEmpty()) {
        fsQueryDTO.setQueryOnline(generateSQL(query, true).toSqlString(new SparkSqlDialect(SqlDialect.EMPTY_CONTEXT)).getSql());
    }
    if (pitEnabled) {
        fsQueryDTO.setPitQuery(makePitQuery(query, isTrainingDataset));
    }
    return fsQueryDTO;
}
Also used : SparkSqlDialect(org.apache.calcite.sql.dialect.SparkSqlDialect) ArrayList(java.util.ArrayList)

Example 19 with SparkSqlDialect

use of org.apache.calcite.sql.dialect.SparkSqlDialect in project hopsworks by logicalclocks.

the class TestConstructorController method testSingleSideSQLQuery.

@Test
public void testSingleSideSQLQuery() throws Exception {
    ConstructorController constructorController = new ConstructorController();
    List<Feature> availableLeft = new ArrayList<>();
    availableLeft.add(new Feature("ft1", "fg0", true));
    Query singleSideQuery = new Query("fs1", "project_fs1", fg1, "fg0", availableLeft, availableLeft);
    String query = constructorController.generateSQL(singleSideQuery, false).toSqlString(new SparkSqlDialect(SqlDialect.EMPTY_CONTEXT)).getSql().replace("\n", " ");
    Assert.assertEquals("SELECT `fg0`.`ft1` FROM `fs1`.`fg1_1` `fg0`", query);
}
Also used : SparkSqlDialect(org.apache.calcite.sql.dialect.SparkSqlDialect) ArrayList(java.util.ArrayList) Test(org.junit.Test)

Example 20 with SparkSqlDialect

use of org.apache.calcite.sql.dialect.SparkSqlDialect in project hopsworks by logicalclocks.

the class TestConstructorController method testSingleOrderBySideSQLQuery.

@Test
public void testSingleOrderBySideSQLQuery() throws Exception {
    ConstructorController constructorController = new ConstructorController();
    List<Feature> availableLeft = new ArrayList<>();
    availableLeft.add(new Feature("ft1", "fg0", true));
    Query singleSideQuery = new Query("fs1", "project_fs1", fg1, "fg0", availableLeft, availableLeft);
    singleSideQuery.setOrderByFeatures(availableLeft);
    String query = constructorController.generateSQL(singleSideQuery, false).toSqlString(new SparkSqlDialect(SqlDialect.EMPTY_CONTEXT)).getSql().replace("\n", " ");
    Assert.assertEquals("SELECT `fg0`.`ft1` FROM `fs1`.`fg1_1` `fg0` ORDER BY `fg0`.`ft1`", query);
}
Also used : SparkSqlDialect(org.apache.calcite.sql.dialect.SparkSqlDialect) ArrayList(java.util.ArrayList) Test(org.junit.Test)

Aggregations

SparkSqlDialect (org.apache.calcite.sql.dialect.SparkSqlDialect)43 Test (org.junit.Test)41 ArrayList (java.util.ArrayList)27 Join (io.hops.hopsworks.common.featurestore.query.join.Join)21 Query (io.hops.hopsworks.common.featurestore.query.Query)14 Feature (io.hops.hopsworks.common.featurestore.query.Feature)13 SqlCondition (io.hops.hopsworks.persistence.entity.featurestore.trainingdataset.SqlCondition)10 SqlFilterLogic (io.hops.hopsworks.persistence.entity.featurestore.trainingdataset.SqlFilterLogic)10 SqlNode (org.apache.calcite.sql.SqlNode)7 HiveSqlDialect (org.apache.calcite.sql.dialect.HiveSqlDialect)5 Collection (java.util.Collection)3 Filter (io.hops.hopsworks.common.featurestore.query.filter.Filter)2 FilterLogic (io.hops.hopsworks.common.featurestore.query.filter.FilterLogic)2 SQLException (java.sql.SQLException)1 SqlCall (org.apache.calcite.sql.SqlCall)1 SqlSelect (org.apache.calcite.sql.SqlSelect)1 AccessSqlDialect (org.apache.calcite.sql.dialect.AccessSqlDialect)1 AnsiSqlDialect (org.apache.calcite.sql.dialect.AnsiSqlDialect)1 BigQuerySqlDialect (org.apache.calcite.sql.dialect.BigQuerySqlDialect)1 CalciteSqlDialect (org.apache.calcite.sql.dialect.CalciteSqlDialect)1