feat(csharp/src/Drivers/Databricks): Optimize GetColumnsExtendedAsync via DESC TABLE EXTENDED #2953

jackyhu-db · 2025-06-11T00:06:08Z

Optimize GetColumnsExtendedAsync in Databricks via `DESC TABLE EXTENDED`

Motivation

Currently, HiveServer2Statement.GetColumnsExtendedAsync will make 3 thrift calls GetColumns, GetPrimaryKeys and GetCrossReference to get all the information and then join them into one result set. Now Databricks introduces a SQL DESC TABLE EXTENDED <table> AS JSON that will return all of these information in one query, this can save 2 extra roundtrips and improve the performance.

Description

Override HiveServer2Statement.GetColumnsExtendedAsync in DatabricksStatement by executing single SQL DESC TABLE EXTENDED <table> AS JSON to get all the required column info for GetColumnsExtendedAsync then combine and join these info into the result. As this SQL DESC TABLE EXTENDED <table> AS JSON is only available in Databricks Runtime 16.2 or above, it will check the ServerProtocolVersion. if it does not meet the requirement, it will fallback the implementation of base class.

Change

Added DescTableExtendedResult that represents the result of DESC TABLE EXTENDED <table> AS JSON (see format here). It also
- Parses the string-based table_constraints to the structured primary key and foreign key constraints
- Adds calculated properties DataType, ColumnSize, FullTypeName which are calculated from the column type and type specific properties
Changed the access modifiers of some properties and methods in HiveServer2Statement to protected so that they can be used/overridden in subclass DatabricksStatement
- PrimaryKeyFields
- ForeignKeyFields
- PrimaryKeyPrefix
- ForeignKeyPrefix
- GetColumnsExtendedAsync
- CreateEmptyExtendedColumnsResult
Updated DatabricksStatement with the changes below
- Added GetColumnsExtendedAsync in DatabricksStatement, it executes query DESC TABLE EXTENDED <table> AS JSON to get all the required info and then json them into the QueryResult
- Moved the column metadata schema creation logic from GetColumnsAsync to a reusable method CreateColumnMetadataSchema
- Moved the column metadata data array creation logic from GetColumnsAsync to a reusable method CreateColumnMetadataEmptyArray
Added a Databricks connection Parameter adbc.databricks.use_desc_table_extended
Added an internal calculated property CanUseDescTableExtended in DatabricksConnection, DatabricksStatement will call it to decide if it will override GetColumnsExtendedAsync
Improved the Databricks driver integration test StatementTest:CanGetColumnsExtended
- Setup the required table resources during the test instead of depending on manual setup before the test
- Support running multiple test cases from the inputs
- Add a deep result check to make sure all the properties of all the columns are set correctly

Testing

Added Test class DescTableExtendedResultTest to cover all the deserialization and parsing cases from raw result of DESC TABLE EXTENDED <table> AS JSON
Tested all the ColumnsExtended relevant test cases in Databricks/StatementTest

…xtended to implement GetColumnsExtendedAsync

eric-wang-1990 · 2025-06-11T21:07:06Z

csharp/test/Drivers/Databricks/Result/DescTableExtendedResultTest.cs

+* limitations under the License.
+*/
+
+using Apache.Arrow.Adbc.Drivers.Databricks.Result;


Can we mocify/improve the existing GetColumnExtended tests to verify that:
With the flag on/off, they get same number of columns, same schema, and same data?

I updated it to support test for both flag on/off as below, they are almost same except for the IS_NULLABLE on primary key.

[InlineData("all_column_types", "Resources/create_table_all_types.sql", "Resources/result_get_column_extended_all_types.json", true, new[] { "PK_IS_NULLABLE:NO" })] [InlineData("all_column_types", "Resources/create_table_all_types.sql", "Resources/result_get_column_extended_all_types.json", false, new[] { "PK_IS_NULLABLE:YES" })] public async Task CanGetColumnsExtended(string tableName,string createTableSqlLocation, string resultLocation, bool useDescTableExtended, string[] ?extraPlaceholdsInResult = null)

CurtHagenlocher

Thanks, looks good! I've just got a few nits about formatting and casing of method names.

csharp/src/Drivers/Apache/Hive2/HiveServer2Statement.cs

csharp/src/Drivers/Databricks/DatabricksConnection.cs

csharp/src/Drivers/Databricks/DatabricksStatement.cs

csharp/src/Drivers/Databricks/Result/DescTableExtendedResult.cs

CurtHagenlocher

Thanks! The changes look good but there's a merge conflict. Could you rebase on top of the latest sourced?

jackyhu-db · 2025-06-24T22:47:21Z

Thanks! The changes look good but there's a merge conflict. Could you rebase on top of the latest sourced?

Done.

eric-wang-1990

LGTM!

jackyhu-db added 13 commits June 8, 2025 19:11

Implement GetColumnsExtendedAsync via DESC TABLE EXTENDED

19f6903

Add DescTableExtendedResult

fc89b32

Change to use DescTableExtendedResult

3bcf3e2

Implement CreateExtendedColumnsResult

55ceff4

Fix executeQuery and get E2E work

4621acb

Add DataType mapping support

99142b6

Add IsNumber in DescTableExtendedResult to set numPrecRadix

80e9634

Support column size

aee8bb2

Support FullTypeName

28b7c4e

Fix autoInremental column

8be918a

Add ServerProtocolVersion property in HiveServer2Connection

c4b4199

Add a protocol version check and flag check before using desc table e…

8bf1d19

…xtended to implement GetColumnsExtendedAsync

Merge remote-tracking branch 'adbc/main' into desc-table-extended

4f04d8d

jackyhu-db requested a review from CurtHagenlocher as a code owner June 11, 2025 00:06

github-actions bot modified the milestone: ADBC Libraries 19 Jun 11, 2025

Fix format issues

7d83b75

jackyhu-db force-pushed the desc-table-extended branch from 81b326f to 7d83b75 Compare June 11, 2025 02:05

jackyhu-db added 2 commits June 11, 2025 12:36

Add fallback when query execution fails

c9fa98f

Add CHAR and VARCHAR length support

8c73c0b

jackyhu-db force-pushed the desc-table-extended branch from 49b3f6e to 8c73c0b Compare June 11, 2025 04:36

Fix some properties of some column types

a6980a1

eric-wang-1990 reviewed Jun 11, 2025

View reviewed changes

jackyhu-db added 2 commits June 12, 2025 09:17

Fix column size for decimal type

f2d5747

Add FK_NAME and KEQ_SEQ fields

d9fd6e8

jackyhu-db force-pushed the desc-table-extended branch 2 times, most recently from 4d4af63 to 4a0d05a Compare June 12, 2025 03:53

Improve Integration test Databricks CanGetColumnsExtended

bb93562

jackyhu-db force-pushed the desc-table-extended branch from 4a0d05a to bb93562 Compare June 12, 2025 04:19

Fix a hard-code ref table name

f7cf526

jackyhu-db added 4 commits June 13, 2025 11:03

Change Strng column size to Int.Max

241d75a

Set column size 0 if column type does not have column size

5b18938

Add space between key/value in Map type name

34dc30d

Fix column size for interval type

03220d2

CurtHagenlocher requested changes Jun 19, 2025

View reviewed changes

jackyhu-db added 4 commits June 24, 2025 18:51

Merge remote-tracking branch 'adbc/main' into desc-table-extended

82643c2

Merge PR 2959

b1b2acb

Address review comments and fix format

4cfffd3

Fix spell error

aa7a763

jackyhu-db force-pushed the desc-table-extended branch from ba704ef to aa7a763 Compare June 24, 2025 11:39

jackyhu-db added 2 commits June 24, 2025 20:03

Fix foreign key column order

3ac76e3

Add the test CanGetColumnsExtended for hiveStatement test

ea557ca

jackyhu-db requested review from CurtHagenlocher and eric-wang-1990 June 24, 2025 12:45

CurtHagenlocher requested changes Jun 24, 2025

View reviewed changes

Merge remote-tracking branch 'adbc/main' into desc-table-extended

ae542b3

jackyhu-db requested a review from CurtHagenlocher June 24, 2025 22:46

eric-wang-1990 approved these changes Jun 24, 2025

View reviewed changes

CurtHagenlocher approved these changes Jun 24, 2025

View reviewed changes

CurtHagenlocher merged commit 0000e8d into apache:main Jun 24, 2025
7 checks passed

serramatutu mentioned this pull request Aug 12, 2025

Sync upstream dbt-labs/arrow-adbc#52

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

feat(csharp/src/Drivers/Databricks): Optimize GetColumnsExtendedAsync via DESC TABLE EXTENDED #2953

feat(csharp/src/Drivers/Databricks): Optimize GetColumnsExtendedAsync via DESC TABLE EXTENDED #2953

Uh oh!

jackyhu-db commented Jun 11, 2025 •

edited

Loading

Uh oh!

eric-wang-1990 Jun 11, 2025

Uh oh!

jackyhu-db Jun 24, 2025

Uh oh!

CurtHagenlocher left a comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

CurtHagenlocher left a comment

Uh oh!

jackyhu-db commented Jun 24, 2025

Uh oh!

eric-wang-1990 left a comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

feat(csharp/src/Drivers/Databricks): Optimize GetColumnsExtendedAsync via DESC TABLE EXTENDED #2953

feat(csharp/src/Drivers/Databricks): Optimize GetColumnsExtendedAsync via DESC TABLE EXTENDED #2953

Uh oh!

Conversation

jackyhu-db commented Jun 11, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Optimize GetColumnsExtendedAsync in Databricks via DESC TABLE EXTENDED

Motivation

Description

Change

Testing

Uh oh!

eric-wang-1990 Jun 11, 2025

Choose a reason for hiding this comment

Uh oh!

jackyhu-db Jun 24, 2025

Choose a reason for hiding this comment

Uh oh!

CurtHagenlocher left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

CurtHagenlocher left a comment

Choose a reason for hiding this comment

Uh oh!

jackyhu-db commented Jun 24, 2025

Uh oh!

eric-wang-1990 left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

jackyhu-db commented Jun 11, 2025 •

edited

Loading

Optimize GetColumnsExtendedAsync in Databricks via `DESC TABLE EXTENDED`