feat: Handle passing of arrays to in statements more efficiently in SQLAlchemy 1.4 and higher

[jimfulton]

Thank you for opening a Pull Request! Before submitting your PR, there are a few things you can do to make sure it goes smoothly:

• Make sure to open an issue as a bug/issue before writing your code! That way we can discuss the change, evaluate designs, and agree on the general idea
• Ensure the tests and linter pass
• Code coverage does not decrease (if any source code was changed)
• Appropriate docs were updated (if necessary)

Fixes #194 🦕

[jimfulton]

For reviewers:

When dealing with an in expression, like sqlalchemy.literal(-1).in_(list(range(99999))) (see test_huge_in), SQLAlchemy wants to turn each element of the passed list into a separate parameter, which is inefficient and breaks for large lists.

BigQuery lets you pass array parameters, which is much better for this case.

See the comment here: https://github.com/googleapis/python-bigquery-sqlalchemy/pull/253/files#diff-efe1987a73ae5b54aa817dc8c70541025df89d9d08601c8ab33fa84ad8c09de7R421-R441

For SQLAlchemy 1.4, it's fairly easy to disable this behavior. (I beat my head against earlier versions and gave up. :)) The only trick is to make sure we call UNNEST on the parameter in SQL. We have to do that in the non-array case too, although it's more complicated.

Finally, in-related tests:

• Have to expect different SQL for SQLAlchemy 1.4 and earlier versions.
• For unit tests, because we're using sqlite, we don't get correct results, because sqlite doesn't support arrays, so we only check the SQL generated.

[tswast]

Should the type of param_2 be ARRAY<INT64>?

[jimfulton]

No, because the BQ DB API does special handling of arrays.

It sees that we have a scalar type of INT64 and that we have a sequence, and then creates a ArrayQueryParameter.

It happens that since I added struct support, passing ARRAY<INT64> would probably work (because I have to handle structs of arrays). But just usng INT64 works too.