Selecting by Name

We wish to select one or more column from a table, and we wish to identify the columns to be selected by specifying their name(s).

Single Column

We wish to select and extract a single column from a table by specifying its name.

SELECT col_1
FROM refcon.dataset.table_1;

Here is how this works:

  • We specify the column name after the SELECT keyword.
  • We specify the table name after FROM keyword.

List of Columns

We wish to specify the columns that we wish to select by their name.

SELECT col_1, col_2
FROM refcon.dataset.table_1;

Here is how this works:

  • We specify the columns names after the SELECT keyword separated by commas.
  • We specify the table name after FROM keyword.

All Columns

We wish to select all columns without spelling their names.

SELECT *
FROM refcon.dataset.table_1;

Range of Columns

We wish to return every column between a given start column and end column including both start and end. We wish to specify the start and end column by their name.

BEGIN
    DECLARE v_start_col STRING;
    DECLARE v_end_col STRING;
    DECLARE v_table_name STRING;
    DECLARE v_columns STRING;

    SET v_start_col = 'col_1';
    SET v_end_col = 'col_3';
    SET v_table_name = 'table_1';
    SET v_columns = (WITH table_columns AS
                              (SELECT column_name, ordinal_position
                               FROM refcon.dataset.INFORMATION_SCHEMA.COLUMNS
                               WHERE table_name = v_table_name)
                     SELECT STRING_AGG(column_name) AS columns
                     FROM table_columns
                     WHERE ordinal_position >= (SELECT ordinal_position
                                                FROM table_columns
                                                WHERE column_name = v_start_col)
                       AND ordinal_position <= (SELECT ordinal_position
                                                FROM table_columns
                                                WHERE column_name = v_end_col));

    EXECUTE IMMEDIATE FORMAT("""SELECT %s
                                FROM refcon.dataset.%s
                             """, v_columns, v_table_name);

END;

Here is how this works:

  • First we define and set our variables, so we can use this snippet with any table.
  • INFORMATION_SCHEMA.COLUMNS contains the columns info for each table in the dataset. We get the column names and their position as table_columns CTE.
  • We filter out columns with positions outside the range of the start and end column position using the WHERE clause. We get the position of the start and end columns using a subquery.
  • We apply STRING_AGG to get the column names as a comma separated string. i.e col_1,col_2,col_3 and set the v_columns variable.
  • We use EXECUTE IMMEDIATE to execute our dynamic query using the v_table and v_columns variables.
  • We achieve this using the FORMAT function which formats a data type expression as a string. %s will be replaced with the variable values in the order of listing the variables.
  • The output of FORMAT will be SELECT col_1,col_2,col_3 FROM refcon.dataset.table_1.
SQL
I/O