Datasets<\/b><\/p>\n
In BigQuery, you have entities called <\/span>datasets<\/span><\/i>. A dataset is nothing more than a container on the top-level of your project used to organize and control access to your tables and views. Thus, a table has to have a dataset it belongs to, so before building your first table, you first have to create a dataset.<\/span><\/p>\n When getting started, it’s OK to see datasets as a simple grouping mechanism, even though under the hood, it is, of course, a bit more.<\/span><\/p>\n Tables and table schemas<\/b><\/p>\n Google BigQuery is a relational database and uses a table structure to organize individual records in rows, while each record consists of columns that are also called <\/span>fields<\/span><\/i>.<\/span><\/p>\n Also, typical for RDBMS, Google defines BigQuery tables by a table schema composed of the columns and their definition. A table’s schema can either be applied at table creation or auto-detected by BigQuery when the first set of data populates the table, even though I would always recommend defining the schema before-hand.<\/span><\/p>\n If you have experience using SQL-based RDBMS, you will quickly get into BigQuery.<\/span><\/p>\n Columns<\/b><\/p>\n BigQuery defines the columns\/fields of a table schema via four different properties.<\/span><\/p>\n Column name<\/b><\/p>\n The column name has to be a case-insensitive alphanumerical string with a maximum of 128 characters. Underscores are also allowed, and the name has to start with either a letter or underscore.\u00a0<\/span><\/p>\n Even though there are three names columns are not allowed to have:<\/span><\/p>\n Column description<\/b><\/p>\n The column description is an optional string describing the column with a maximum of 1,024 characters.<\/span><\/p>\n Type<\/b><\/p>\n The type is a mandatory field describing the data type of the column. Possible values are, e.g.:\u00a0<\/span><\/p>\n You can find a list of all possible values in the <\/span>official BigQuery docs<\/span><\/a>.<\/span><\/p>\n Modes<\/b><\/p>\n Each column also can have a so-called optional mode. The mode of a column can be one of three possible values.<\/span><\/p>\n Repeated<\/span><\/i> \u2014 The field contains an array of values of the specified data type instead of a single value.<\/span><\/p>\n <\/p>\n\n\n After defining the absolute basics one should know before getting started with BigQuery, we will now set up a small BigQuery project using Node.js. <\/span>Since this is an article using Node.js, I expect that Node.js and NPM are available on the readers’ machine.<\/span><\/p>\n Setup of a clean Node.js project using BigQuery<\/b><\/p>\n GCP service account<\/b><\/p>\n Regardless of application size, I always recommend creating an extra service account via GCP IAM to ensure security and correct permissions are in place, regardless of who runs the application. How to create an additional service account in detail is outside the scope of this article but can be simplified in a few easy steps:<\/span><\/p>\n Make sure to add the key file to your just created Node.js project. Just be aware that you should never commit these critical files to Git! If you want to know how to store your secrets in Git securely, <\/span>you can check out one of my other articles on medium<\/span><\/a>:<\/span><\/p>\n Creating datasets and tables via code<\/b><\/p>\n\n
\n
\n
Hands-on BigQuery<\/h2>\n
\n
\n
![\"bigquery](\"https:\/\/usercentrics.com\/wp-content\/uploads\/sites\/7\/2021\/05\/CODE_SNIPPET_CREATE_DATASET_TABLE.png\")
![\"GCP_BIGQUERY\"](\"https:\/\/usercentrics.com\/wp-content\/uploads\/sites\/7\/2021\/04\/GCP_BIGQUERY.png\")