generateChangeLog creates an invalid changelog for array fields

[gabrieldeal]

Community Note

• Please vote on this issue by adding a 👍 reaction
  to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do
  not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment.

Debug Output

https://gist.github.com/gabrielmdeal/85ba1470346861ef87779027556218ff

Also part of the gist is Array_Repro_Table.json, and the changelog.json that liquibase generateChangeLog creates.

Panic Output

N/A

Steps to Reproduce

TLDR: ARRAY and STRUCT column types don't seem to work with liquibase generateChangeLog. I can't find documentation explaining if it is possible to manually create JSON for these types of columns. Nor can I find documentation that says these types are unsupported.

1. Create multi-region datasets named Gabriel_Array and Gabriel_Array_Target
2. Get Array_Repro_Table.json from the gist above.
3. bq mk Gabriel_Array.Array_Repro_Table Array_Repro_Table.json
4. Run liquibase generateChangeLog on Gabriel_Array
5. Run liquibase update on Gabriel_Array_Target

I ran into a similar issue with a STRUCT column.

Expected Behavior

One of these:

• The Array_Repro_Table is created in Gabriel_Array_Target. 🎉
• liquibase generateChangeLog fails with an error saying that arrays and structs are not supported for Big Query.
• The documentation says that arrays and structs are not supported.

Actual Behavior

liquibase update fails with this error: liquibase.exception.DatabaseException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: Syntax error: Expected "<" but got "(" at [1:72] [Failed SQL: (100032) CREATE TABLE Gabriel_Array_Target.Array_Repro_Table (Array_Column ARRAY(65535))] See the gist for the full log.

Workaround

If I use --changeLogFile=changelog.bigquery.sql, then liquibase generateChangeLog still generates an invalid changelog.sql. But I am able to manually fix the SQL and then successfully run liquibase update.

[gabrieldeal]

After I manually created a changelog, I found that liquibase update also works on a multi-region dataset, but fails with the below error on a regional dataset.

[2023-05-17 00:57:45] FINE [liquibase.executor] CREATE TABLE Gabriel_Liquibase_Target.DATABASECHANGELOGLOCK (ID INT, LOCKED BOOLEAN, LOCKGRANTED datetime, LOCKEDBY STRING(255))
[2023-05-17 00:57:46] FINE [liquibase.lockservice] Failed to create or initialize the lock table, trying again, iteration 1 of 10
liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: Not found: Dataset redacted-project-id:Gabriel_Liquibase_Target was not found in location US [Failed SQL: (100032) CREATE TABLE Gabriel_Liquibase_Target.DATABASECHANGELOGLOCK (ID INT, LOCKED BOOLEAN, LOCKGRANTED datetime, LOCKEDBY STRING(255))]
	at liquibase.executor.jvm.ChangelogJdbcMdcListener.execute(ChangelogJdbcMdcListener.java:39)
	at liquibase.lockservice.StandardLockService.init(StandardLockService.java:117)
	at liquibase.lockservice.StandardLockService.acquireLock(StandardLockService.java:293)
	at liquibase.lockservice.StandardLockService.waitForLock(StandardLockService.java:254)
	at liquibase.command.core.helpers.LockServiceCommandStep.run(LockServiceCommandStep.java:37)
	at liquibase.command.CommandScope.execute(CommandScope.java:212)
	at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:55)