Examples with QueryResult io.trino.tempto.query.QueryResult used on opensource projects

Example 66 with QueryResult

use of io.trino.tempto.query.QueryResult in project trino by trinodb.

the class TestIcebergSparkCompatibility method testTrinoWritingDataWithWriterDataPathSet.

@Test(groups = { ICEBERG, PROFILE_SPECIFIC_TESTS }, dataProvider = "storageFormatsWithSpecVersion")
public void testTrinoWritingDataWithWriterDataPathSet(StorageFormat storageFormat, int specVersion) {
    String baseTableName = "test_writer_data_path_" + storageFormat;
    String sparkTableName = sparkTableName(baseTableName);
    String trinoTableName = trinoTableName(baseTableName);
    String dataPath = "hdfs://hadoop-master:9000/user/hive/warehouse/test_writer_data_path_/obj-data";
    onSpark().executeQuery(format("CREATE TABLE %s (_string STRING, _bigint BIGINT) USING ICEBERG TBLPROPERTIES (" + "'write.data.path'='%s'," + "'write.format.default' = '%s'," + "'format-version' = %s)", sparkTableName, dataPath, storageFormat, specVersion));
    onTrino().executeQuery(format("INSERT INTO %s VALUES ('a_string', 1000000000000000)", trinoTableName));
    Row result = row("a_string", 1000000000000000L);
    assertThat(onSpark().executeQuery(format("SELECT _string, _bigint FROM %s", sparkTableName))).containsOnly(result);
    assertThat(onTrino().executeQuery(format("SELECT _string, _bigint FROM %s", trinoTableName))).containsOnly(result);
    QueryResult queryResult = onTrino().executeQuery(format("SELECT file_path FROM %s", trinoTableName("\"" + baseTableName + "$files\"")));
    assertThat(queryResult).hasRowsCount(1).hasColumnsCount(1);
    assertTrue(((String) queryResult.row(0).get(0)).contains(dataPath));
    assertQueryFailure(() -> onTrino().executeQuery("DROP TABLE " + trinoTableName)).hasMessageContaining("contains Iceberg path override properties and cannot be dropped from Trino");
    onSpark().executeQuery("DROP TABLE " + sparkTableName);
}

Example 67 with QueryResult

use of io.trino.tempto.query.QueryResult in project trino by trinodb.

the class TestIcebergSparkCompatibility method testMigratedDataWithAlteredSchema.

@Test(groups = { ICEBERG, PROFILE_SPECIFIC_TESTS }, dataProvider = "storageFormats")
public void testMigratedDataWithAlteredSchema(StorageFormat storageFormat) {
    String baseTableName = "test_migrated_data_with_altered_schema_" + randomTableSuffix();
    String defaultCatalogTableName = sparkDefaultCatalogTableName(baseTableName);
    String sparkTableDefinition = "" + "CREATE TABLE %s (\n" + "  doc_id STRING\n" + ", nested_struct STRUCT<id:INT, name:STRING, address:STRUCT<a:INT, b:STRING>>)\n" + " USING %s";
    onSpark().executeQuery(format(sparkTableDefinition, defaultCatalogTableName, storageFormat.name()));
    String insert = "" + "INSERT INTO TABLE %s SELECT" + "  'Doc213'" + ", named_struct('id', 1, 'name', 'P. Sherman', 'address', named_struct('a', 42, 'b', 'Wallaby Way'))";
    onSpark().executeQuery(format(insert, defaultCatalogTableName));
    onSpark().executeQuery(format("CALL system.migrate('%s')", defaultCatalogTableName));
    String sparkTableName = sparkTableName(baseTableName);
    onSpark().executeQuery("ALTER TABLE " + sparkTableName + " RENAME COLUMN nested_struct TO nested_struct_moved");
    String select = "SELECT" + " nested_struct_moved.name" + ", nested_struct_moved.address.a" + ", nested_struct_moved.address.b" + "  FROM ";
    Row row = row("P. Sherman", 42, "Wallaby Way");
    QueryResult sparkResult = onSpark().executeQuery(select + sparkTableName);
    assertThat(sparkResult).containsOnly(ImmutableList.of(row));
    String trinoTableName = trinoTableName(baseTableName);
    assertThat(onTrino().executeQuery(select + trinoTableName)).containsOnly(ImmutableList.of(row));
    // After removing the name mapping, columns from migrated files should be null since they are missing the Iceberg Field IDs
    onSpark().executeQuery(format("ALTER TABLE %s UNSET TBLPROPERTIES ('schema.name-mapping.default')", sparkTableName));
    assertThat(onTrino().executeQuery(select + trinoTableName)).containsOnly(row(null, null, null));
}

Example 68 with QueryResult

use of io.trino.tempto.query.QueryResult in project trino by trinodb.

the class TestIcebergSparkCompatibility method testTrinoWritingDataWithObjectStorageLocationProvider.

@Test(groups = { ICEBERG, PROFILE_SPECIFIC_TESTS }, dataProvider = "storageFormatsWithSpecVersion")
public void testTrinoWritingDataWithObjectStorageLocationProvider(StorageFormat storageFormat, int specVersion) {
    String baseTableName = "test_object_storage_location_provider_" + storageFormat;
    String sparkTableName = sparkTableName(baseTableName);
    String trinoTableName = trinoTableName(baseTableName);
    String dataPath = "hdfs://hadoop-master:9000/user/hive/warehouse/test_object_storage_location_provider/obj-data";
    onSpark().executeQuery(format("CREATE TABLE %s (_string STRING, _bigint BIGINT) USING ICEBERG TBLPROPERTIES (" + "'write.object-storage.enabled'=true," + "'write.object-storage.path'='%s'," + "'write.format.default' = '%s'," + "'format-version' = %s)", sparkTableName, dataPath, storageFormat, specVersion));
    onTrino().executeQuery(format("INSERT INTO %s VALUES ('a_string', 1000000000000000)", trinoTableName));
    Row result = row("a_string", 1000000000000000L);
    assertThat(onSpark().executeQuery(format("SELECT _string, _bigint FROM %s", sparkTableName))).containsOnly(result);
    assertThat(onTrino().executeQuery(format("SELECT _string, _bigint FROM %s", trinoTableName))).containsOnly(result);
    QueryResult queryResult = onTrino().executeQuery(format("SELECT file_path FROM %s", trinoTableName("\"" + baseTableName + "$files\"")));
    assertThat(queryResult).hasRowsCount(1).hasColumnsCount(1);
    assertTrue(((String) queryResult.row(0).get(0)).contains(dataPath));
    // TODO: support path override in Iceberg table creation: https://github.com/trinodb/trino/issues/8861
    assertQueryFailure(() -> onTrino().executeQuery("DROP TABLE " + trinoTableName)).hasMessageContaining("contains Iceberg path override properties and cannot be dropped from Trino");
    onSpark().executeQuery("DROP TABLE " + sparkTableName);
}

Example 69 with QueryResult

use of io.trino.tempto.query.QueryResult in project trino by trinodb.

the class TestIcebergSparkCompatibility method testSparkReadingNestedTrinoData.

@Test(groups = { ICEBERG, PROFILE_SPECIFIC_TESTS }, dataProvider = "storageFormats")
public void testSparkReadingNestedTrinoData(StorageFormat storageFormat) {
    String baseTableName = "test_spark_reading_nested_trino_data_" + storageFormat;
    String trinoTableName = trinoTableName(baseTableName);
    String sparkTableName = sparkTableName(baseTableName);
    onTrino().executeQuery(format("CREATE TABLE %s (\n" + "  doc_id VARCHAR\n" + ", nested_map MAP(VARCHAR, ARRAY(ROW(sname VARCHAR, snumber INT)))\n" + ", nested_array ARRAY(MAP(VARCHAR, ARRAY(ROW(mname VARCHAR, mnumber INT))))\n" + ", nested_struct ROW(name VARCHAR, complicated ARRAY(MAP(VARCHAR, ARRAY(ROW(mname VARCHAR, mnumber INT))))))" + "  WITH (format = '%s')", trinoTableName, storageFormat));
    onTrino().executeQuery(format("INSERT INTO %s SELECT" + "  'Doc213'" + ", map(array['s1'], array[array[row('ASName1', 201), row('ASName2', 202)]])" + ", array[map(array['m1'], array[array[row('MAS1Name1', 301), row('MAS1Name2', 302)]])" + "       ,map(array['m2'], array[array[row('MAS2Name1', 401), row('MAS2Name2', 402)]])]" + ", row('S1'" + "      ,array[map(array['m1'], array[array[row('SAMA1Name1', 301), row('SAMA1Name2', 302)]])" + "            ,map(array['m2'], array[array[row('SAMA2Name1', 401), row('SAMA2Name2', 402)]])])", trinoTableName));
    Row row = row("Doc213", "ASName2", 201, "MAS2Name1", 302, "SAMA1Name1", 402);
    assertThat(onTrino().executeQuery("SELECT" + "  doc_id" + ", nested_map['s1'][2].sname" + ", nested_map['s1'][1].snumber" + ", nested_array[2]['m2'][1].mname" + ", nested_array[1]['m1'][2].mnumber" + ", nested_struct.complicated[1]['m1'][1].mname" + ", nested_struct.complicated[2]['m2'][2].mnumber" + "  FROM " + trinoTableName)).containsOnly(row);
    QueryResult sparkResult = onSpark().executeQuery("SELECT" + "  doc_id" + ", nested_map['s1'][1].sname" + ", nested_map['s1'][0].snumber" + ", nested_array[1]['m2'][0].mname" + ", nested_array[0]['m1'][1].mnumber" + ", nested_struct.complicated[0]['m1'][0].mname" + ", nested_struct.complicated[1]['m2'][1].mnumber" + "  FROM " + sparkTableName);
    assertThat(sparkResult).containsOnly(row);
    onTrino().executeQuery("DROP TABLE " + trinoTableName);
}

Example 70 with QueryResult

use of io.trino.tempto.query.QueryResult in project trino by trinodb.

the class TestHiveStorageFormats method assertResultEqualForLineitemTable.

/**
 * Run the given query on the given table and the TPCH {@code lineitem} table
 * (in the schema {@code TPCH_SCHEMA}, asserting that the results are equal.
 */
private static void assertResultEqualForLineitemTable(String query, String tableName) {
    QueryResult expected = onTrino().executeQuery(format(query, "tpch." + TPCH_SCHEMA + ".lineitem"));
    List<Row> expectedRows = expected.rows().stream().map(columns -> row(columns.toArray())).collect(toImmutableList());
    QueryResult actual = onTrino().executeQuery(format(query, tableName));
    assertThat(actual).hasColumns(expected.getColumnTypes()).containsExactlyInOrder(expectedRows);
}