Get all assets that are columns in a schema

1.4.0 1.1.0

This example finds all columns that exist in a particular schema — irrespective of the table, view, or materialized view they exist within.

Java Python Kotlin Raw REST API

String schemaQN = "default/snowflake/1662194632/MYDB/MY_SCH"; // (1)
Column.select() // (2)
    .where(Asset.QUALIFIED_NAME.startsWith(schemaQN)) // (3)
    .pageSize(100) // (4)
    .includeOnResults(Asset.DESCRIPTION) // (5)
    .stream() // (6)
    .filter(a -> a instanceof Column) // (7)
    .forEach(c -> { // (8)
        log.info("Column: {}", c);
    });

1. Part of the trick here is that the qualifiedName of a column starts with the qualifiedName of its parent (table, view or materialized view). Similarly, the qualifiedName of the table, view or materialized view starts with the qualifiedName of its parent schema. (And so on, all the way up to the connection itself.)
2. To start building up a query specifically for columns, you can use the select() convenience method on Column itself.
3. You can use the where() method to define all the conditions the search results must match. For this example, use the Asset.QUALIFIED_NAME constant to limit to only those assets whose qualifiedName starts with the qualifiedName of the schema (by using the startsWith() predicate). In this example, that means only assets that are within this particular schema will be returned as results.
4. (Optional) You can play around with different page sizes, to further limit API calls by retrieving more results per page.
5. Add as many attributes as needed. Each attribute you add here will ensure that detail is included in each search result. So in this example, every column will include its description. (Limit these attributes to the minimum you need about each column to do your intended work.)
6. The search will only run when you call the stream() method, which will then lazily-load each page of results into a stream.
7. (Optional) You can do any other operations you might do on a stream, such as filtering the results to ensure they are of a certain type.
8. This is the pattern for iterating through all results (across pages) covered in the Searching for assets portion of the SDK documentation.

from pyatlan.client.atlan import AtlanClient
from pyatlan.model.assets import Column
from pyatlan.model.fluent_search import FluentSearch, CompoundQuery

schema_qn = "default/snowflake/1646836521/ATLAN_SAMPLE_DATA/PUBLIC"  # (1)
client = AtlanClient()  # (2)
request = (
    FluentSearch()  # (3)
    .where(CompoundQuery.asset_type(Column))  # (4)
    .where(CompoundQuery.active_assets())  # (5)
    .where(Column.QUALIFIED_NAME.startswith(schema_qn))  # (6)
).to_request()  # (7)
for result in client.asset.search(request):  # (8)
    if isinstance(result, Column):  # (9)
        print(result)

1. Part of the trick here is that the qualified_name of a column starts with the qualified_name of its parent (table, view or materialized view). Similarly, the qualified_name of the table, view or materialized view starts with the qualified_name of its parent schema. (And so on, all the way up to the connection itself.)
2. Start with a client to run the search through. For the default client, you can always use AtlanClient().
3. To search across all assets, you can use a FluentSearch object.
4. The .where() method allows you to limit to only certain assets. In this example, we are looking for columns, so use the CompoundQuery.asset_type() helper to narrow to only columns.
5. You can chain additional .where() methods to add further conditions, like this example where we limit to only active (non-archived) assets.
6. For this example, use the Column.QUALIFIED_NAME constant to limit to only those columns whose qualified_name starts with the qualified_name of the schema (by using the startswith() predicate). In this example, that means only columns that are within this particular schema will be returned as results.
7. You can then translate the fluent search into an index search request.
8. This is the pattern for iterating through all results (across pages) covered in the Searching for assets portion of the SDK documentation.
9. Use the isinstance method to ensure that the asset is of the desired type. This will also allow an IDE to provide specific type hints for this asset type.

val schemaQN = "default/snowflake/1662194632/MYDB/MY_SCH" // (1)
Column.select() // (2)
    .where(Asset.QUALIFIED_NAME.startsWith(schemaQN)) // (3)
    .pageSize(100) // (4)
    .includeOnResults(Asset.DESCRIPTION) // (5)
    .stream() // (6)
    .filter { it is Column } // (7)
    .forEach { // (8)
        log.info { "Column: $it" }
    });

1. Part of the trick here is that the qualifiedName of a column starts with the qualifiedName of its parent (table, view or materialized view). Similarly, the qualifiedName of the table, view or materialized view starts with the qualifiedName of its parent schema. (And so on, all the way up to the connection itself.)
2. To start building up a query specifically for columns, you can use the select() convenience method on Column itself.
3. You can use the where() method to define all the conditions the search results must match. For this example, use the Asset.QUALIFIED_NAME constant to limit to only those assets whose qualifiedName starts with the qualifiedName of the schema (by using the startsWith() predicate). In this example, that means only assets that are within this particular schema will be returned as results.
4. (Optional) You can play around with different page sizes, to further limit API calls by retrieving more results per page.
5. Add as many attributes as needed. Each attribute you add here will ensure that detail is included in each search result. So in this example, every column will include its description. (Limit these attributes to the minimum you need about each column to do your intended work.)
6. The search will only run when you call the stream() method, which will then lazily-load each page of results into a stream.
7. (Optional) You can do any other operations you might do on a stream, such as filtering the results to ensure they are of a certain type.
8. This is the pattern for iterating through all results (across pages) covered in the Searching for assets portion of the SDK documentation.

{
  "dsl": { // (1)
    "query": {
      "bool": { // (2)
        "filter": [ // (3)
          {
            "prefix": { // (4)
              "qualifiedName": {
                "value": "default/snowflake/1662194632/MYDB/MY_SCH"
              }
            }
          },
          {
            "term": { // (5)
              "__typeName.keyword": {
                "value": "Column"
              }
            }
          },
          {
            "term": { // (6)
              "__state": {
                "value": "ACTIVE"
              }
            }
          }
        ]
      }
    },
    "from": 0, // (7)
    "size": 100,
    "track_total_hits": true
  },
  "attributes": [ // (8)
    "description"
  ],
  "suppressLogs": true,
  "showSearchScore": false,
  "excludeMeanings": false,
  "excludeClassifications": false
}

1. Run a search to find the columns.

2. To start building up a query with multiple conditions, you can use a bool query in Elasticsearch.

3. You can use the filter criteria to define all the conditions the search results must match in a binary way (either matches or doesn't). This avoids the need to calculate a score for each result.

4. Part of the trick here is that the qualifiedName of a column starts with the qualifiedName of its parent (table, view or materialized view). Similarly, the qualifiedName of the table, view or materialized view starts with the qualifiedName of its parent schema. (And so on, all the way up to the connection itself.)

5. Since there could be tables, views, materialized views and columns in this schema — but you only want columns — you can use an exact match on the type to restrict results to only columns.

6. Searches by default will return all assets that are found — whether active or archived (soft-deleted). In most cases, you probably only want the active ones.

7. Here you can play around with different page sizes, to further limit API calls by retrieving more results per page.

8. Add as many attributes as needed. Each attribute you add here will ensure that detail is included in each search result. So in this example, every column will include its description. (Limit these attributes to the minimum you need about each column to do your intended work.)

---