AQL For Finding a Table w/in a Database

Hi ​@sjettawi ,

thank you for your question. Ataccama stores schemas and databases as locations and these are parents of the catalog items (schema is a parent of a catalog item and database is a parent of the schema). You might have different hierarchies for different technologies (e.g. teradata can have many databases in the hierarchy, file on S3 can have many nested folders as parents), but the principle is the same, all parents will be locations.

AQL for specifically 2 parents (schema and database) will look like this:

$parent.name='dbo' and $parent.$parent.name='sales'

The syntax $parent is used specifically to indicate a parent entity of the searched entity (catalog item in this case). You can read more about it in the documentation. https://docs.ataccama.com/one/latest/catalog-items/aql-expressions.html#combining-scalar-conditions

Let me know if this answers your question.

Kind regards,

Anna

Will this allow me to find an exact table within the database? 

Hi ​@sjettawi ,

this query will give you all tables within the specified database and schema. I am sorry, I probably didn’t understand well what was your requirement. Can you maybe specify what exactly do you need to search for? You can add the table name into the query with and name like ‘your name’.

Kind regards,

Anna

I was able to deduce the information to get what we needed. We have a custom “columns” view that we created, so using that view, the AQL we need is below: 

To get a list of all columns within a specific table:

$parent.name='TABLE NAME' and $parent.$parent.name=SCHEMA NAME' and $parent.$parent.$parent.name='DATABASE NAME'

To get a list of a specific column within a specific table:

name='COLUMN NAME' and $parent.name='TABLE NAME' and $parent.$parent.name='SCHEMA NAME' and $parent.$parent.$parent.name='DATABASE NAME'

To get a list of a specific column across a specific schema:

name='COLUMN NAME' and $parent.$parent.name='SCHEMA NAME'

To get a list of a specific column across a specific database:

name='COLUMN NAME' and $parent.$parent.$parent.name=DATABASE NAME'