Knowledge Drop

Databases: Key Terms


Userlevel 3

Last tested: Dec 9, 2019
 

Permissions and Objects

Databases restrict what (action) a user can do on which objects. The terms for each of these differ slightly from dialect-to-dialect, but themes will remain the same across all.

  • Permission: What a user can do. Most common examples:
    • SELECT : Can run a query (SELECT *, SELECT AVG(some_column), etc).
    • ALTER/UPDATE TABLE : Can change a table in some way. Usually needs more permissions depending on the action we're trying to take on the table, see below.
    • INSERT: Can add rows to a column in a table.
    • DROP TABLE : Can delete a table.
    • CREATE TABLE : Can create a new table in a given schema
    • GRANT : Can change other user's permissions on the given table.
  • Objects: An object is a container for data or containers containing data. Objects are hierarchal (columns live in tables inside schemas inside databases) and permissions can be granted on each object, individually.
    • Database: Databases are themselves objects (meta I know). A database is a grouping of schemas.
    • Schema: A container for tables. Typically, a given schema will contain tables that relate to each-other in some way.
    • Table: A container for columns of data.
    • Column: A container for cells of data. Some databases do not allow column-level permissions.
  • Role: A grouping of permissions over a given set of objects. A user's role could have permission to run SELECT on table1 and no permissions on table2, drop table on tables in schema1 and only select permissions on tables in schema2, etc.
  • Group: A way of bulk-managing multiple user's Roles.

Other terms you may see in the wild:

  • Temp Table: Some databases support the creation of temporary(ish) tables derived from a SQL query. These are roughly equivalent to Looker's concept of Persistent Derived Tables, and can be queried like any other table. Synonyms: Derived Table (MySQL), View, Virtual Table
    • Temp Tables are generally created with the CREATE TEMPORARY TABLE(MySQL) command, or the CREATE VIEW command (in other dialects).
  • Stored Process: A custom-user-defined function that can be used in a query. Basically a snippet of SQL that can be passed parameters and called by referring to it's user-given function name. Synonyms: UDF (User Defined Function -- BigQuery), Stored Procedure
    • For example, we could create a function SUBTRACT(colx, coly) that takes values from column x and column y and subtracts them.
    • UDFs can be defined in and used by Looker in some dialects with the sql_preamble: LookML parameter (see Lloyd's Community article here for a great example).
  • Virtual Warehouse: You'll usually hear this term in the context of Snowflake, however some other databases have a similar notion. In Snowflake, a data warehouse (virtual warehouse) is a SQL engine (the compute power that generates and runs queries) shared by one or more databases.
  • Information Schema: Database tables that store metadata about the database. Users and their permissions, information about objects and the datatypes they contain, and query history will all be stored here.

 

This content is subject to limited support.                

 

 


0 replies

Be the first to reply!

Reply