Saner approaches to getting metadata for Relations about dbt-spark HOT 7 CLOSED

Great find! This looks promising and I imagine it could create significant performance benefits. And thank you for the link to the source code. I traced the blame on this line and it looks like the latest commit was >3 years ago, with even the prior version of that line shown here appearing to support the same syntax.

With that said, my guess is that support for this would likely correlate with spark version number moreso than with vendor, and it appears this has been in at least since version Spark 2.2 and likely longer. (Someone else jump in if you have additional/different info.)

For my part, I think this is a relatively safe bet and likely worth the performance boost. Although due to the noted lack of documentation, I also think some type of safe failover or feature flag might be advisable.

from dbt-spark.

Thanks for the pointer to that, @aaronsteers!

I have updated my original issue comment to reflect the issue on my end (faulty/outdated JDBC driver) that was causing me to encounter errors with variants of describe. That said, show table extended in my_db like '*' is still the closest thing we have to an information schema that we can access all at once.

If it works across the board, I think it offers a more performant approach to the get_catalog updates in #39 and #41, versus running describe table extended for every relation in the project. The difficulty there is in parsing the information column, which is a big string delimited by \n, rather than additional rows per property as in describe table extended.

from dbt-spark.

Related: This feature is a long way out (at best), but here's the last and best reference I could find to the feature request to add INFORMATION_SCHEMA support natively. You can vote and review that Spark Jira issue here: https://issues.apache.org/jira/browse/SPARK-16452 . We cannot block on this feature but I thought it would be helpful to flag it at least here for general awareness.

from dbt-spark.

The difficulty there is in parsing the information column, which is a big string delimited by \n, rather than additional rows per property as in describe table extended.

I think a regex approach could probably make quick work of the column names and data types. I will take a quick stab at that and post back here.

from dbt-spark.

This regex search string seems to work on the sample output from above:

\|-- (.*): (.*) \(nullable = (.*)\b

This regex string outputs the three captured pairs:

• column name
• column type
• nullable (true/false)

Link to test results and demo of this regex: https://regex101.com/r/E5YHCs/1

from dbt-spark.

Very nice!

As far as the

more performant approach to the get_catalog updates

that I mentioned above, I think a natural fit here is the _get_one_catalog method that is introduced in dbt-labs/dbt-core#2037 and will hopefully ship in the 0.16.0 release of dbt Core.

Between now and then, we can still try to ship the proposed enhancements to get_catalog as written (looping across all relations and running describe ... extended) in the 0.15.0 release of this plugin.

from dbt-spark.

I'm going to close this and open a more specific issue that suggests reimplementing _get_one_catalog to operate more efficiently on one schema at a time, instead of one relation at a time.

from dbt-spark.