Catalog Views (U-SQL)
Summary
Catalog views return information that is used by U-SQL. We recommend that you use catalog views because they are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information.
Catalog views will not contain objects that have been created as part of the same script and will only show the objects that the user submitting the query has the rights to see.
The U-SQL catalog views are currently not available in the local run environment.
Future releases will add additional catalog views.
The following catalog views are currently available:
Examples
The examples can be executed in Visual Studio with the Azure Data Lake Tools plug-in.
Query the usql.databases view
USE TestReferenceDB;
OUTPUT usql.databases
TO "/ReferenceGuide/CatalogViews/databases.txt"
USING Outputters.Tsv(outputHeader:true);
Querying multiple catalog views
The following script returns the tables with their fully qualified, quoted names as well as their column information (name and type and maximal possible field size) ordered alphabetically by table and in order of their column positions:
@result =
SELECT "[" + db.name + "].[" + s.name + "].[" + t.name + "]" AS table_name,
c.name AS col_name,
c.column_id AS col_pos,
ct.qualified_name AS col_type,
c.max_length == - 1 ?
ct.qualified_name == "System.String" ?
128 * 1024
: ct.qualified_name == "System.Byte[]" ?
4 * 1024 * 1024
: - 1
: c.max_length AS col_max_length
FROM usql.databases AS db
JOIN usql.schemas AS s ON db.database_id_guid == s.database_id_guid
JOIN usql.tables AS t ON s.schema_id_guid == t.schema_id_guid
JOIN usql.columns AS c ON c.object_id_guid == t.object_id_guid
JOIN usql.types AS ct ON c.type_id_guid == ct.type_id_guid;
OUTPUT @result
TO "/ReferenceGuide/CatalogViews/tableinfo.csv"
ORDER BY table_name, col_pos
USING Outputters.Csv(outputHeader : true);