BigQuery date converted to Exasol VARCHAR(10)

[redcatbear]

Situation

When loading a table from BigQuery that contains a DATE, on the Exasol side it results in a VARCHAR instead of a DATE.

Example: DATE value "09.01.2020" on BigQuery turned to a VARCHAR(10) UTF8 in Exasol.

[ckunki]

Implementation accepts only dates in format YYYY-DD?-DD?, see class BigQueryQueryRewriter:

private static final Pattern DATE_PATTERN = Pattern.compile("(\\d{4})-(\\d{1,2})-(\\d{1,2})"); 

Related unit test in BigQueryQueryRewriterTest.java, expects dates using German date pattern to be interpreted as VARCHAR:

    @CsvSource({ "string_col, 12, hello, hello", //
            "string_col, 12, i'm, i\\'m", //
            "time_col, 92, 12:10:09.000, 12:10:09.000", //
            "numeric_col, 2, 22222.2222, 22222.2222", //
            "numeric_col, 2, 11.5, 11.5", //
            "date_col, 91, 1111-01-01, 01.01.1111", //
            "date_col, 91, 2019-12-3, 03.12.2019", //
            "date_col, 91, 2019-5-02, 02.05.2019" //
    })
    @ParameterizedTest
    void testRewriteWithStringValues(final String columnName, final int type, final String columnValue,
            final String resultValue) throws AdapterException, SQLException {
        assertQueryWithOneStringValue(columnName, type, columnValue, "SELECT * FROM VALUES ('" + resultValue + "')");
    }

Recommendation to user:

• Please use ISO date pattern: YYYY-DD?-DD?

[ckunki]

According to V' the column format in BigQuery is DATE.

See also

• https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#parse_date
• https://stackoverflow.com/questions/62493288/change-the-date-format-in-bigquery
• https://cloud.google.com/sdk/gcloud/reference/beta/emulators

[ckunki]

V' will contact the original requester and will set up an appointment.

[ckunki]

Impression by @kaklakariada and me:

• Current implementation uses cast only for null values but not for datatype date in general.
• Current implementation may work for simple data types such as decimals and Strings but not for date and maybe more.
• Additionally bigquery VS seems to reformat dates wrongly in BigQueryQueryRewriter:

  private String castDate(final String dateToCast) {
  // ...
  return String.format("%02d.%02d.%04d", day, month, year);
  }

[ckunki]

@redcatbear confirmed the initial impressions. Next steps

• inform V'
• find ways to create and run integration tests, ideally using a mock for BigQuery

[kaklakariada]

Blocked by #9