CODEWITHSUNDEEP
azure-databricksinformation-schemadatabricks-sql
drvenom5140
drvenom5140

Reputation: 13

How to read system.information_schema.columns in databricks

How can I read the system table system.information_schema.columns, it take about 3 hours to read and only has 3 million rows, is there any hint or like in Oracle with(nolock) to read this table quickly?

Upvotes: 1

Views: 602

Answers (1)

Dileep Raj Narayan Thumula
Dileep Raj Narayan Thumula

Reputation: 3250

According to documentation provided in the Information schema

The INFORMATION_SCHEMA is a SQL standard-based schema available in every catalog created on Unity Catalog.

Within the information schema, there are views that describe the objects in the schema's catalog that you have permission to see. The information schema of the SYSTEM catalog provides information about objects across all catalogs within the metastore.

Note: that the information schema system tables do not contain metadata about hive_metastore objects.

Know more about the Information schema views

Note: Identifiers are case-insensitive in SQL statements

The following syntax shows the use the system level information schema tables.

SELECT table_name, column_name FROM information_schema.columns WHERE data_type = 'DOUBLE' AND table_schema = 'information_schema';

%sql
SELECT table_name, column_name
FROM information_schema.columns
WHERE data_type = 'DOUBLE' AND table_schema = 'default'

Results:

table_name  column_name
sample_table    name

You can also try the below approach:

%sql
SELECT 
  'dileepdbx.default.sample_table' AS `table`,  label the table reference
  column_name,
  data_type
FROM system.information_schema.columns
WHERE table_name = 'sample_table' 
ORDER BY 1

Results:

table   column_name data_type
dileepdbx.default.sample_table  id  LONG
dileepdbx.default.sample_table  score   DOUBLE
dileepdbx.default.sample_table  name    STRING

Upvotes: 0

Related Questions