Google BigQuery allows users to perform simple manipulations of their data. Any BigQuery user with basic SQL knowledge should be able to perform simple manipulations with ease. This chapter covers the processes of adding data to a table from a flat .csv file, querying a table, inserting rows into tables, updating rows of tables, and deleting rows from tables.
You're reading from Learning Google BigQuery
The BigQuery interface
The BigQuery web interface looks like the following screenshot displaying the current project and the datasets and tables in that project. The COMPOSE QUERY button will open the panel to type the query as shown in the following screenshot:
Data can be inserted, queried, updated, and deleted via the BigQuery API or the Google BigQuery web interface. This section will cover creating and inserting data via the BigQuery web interface. The BigQuery web interface is useful as it provides some tools for manipulation and debugging not seen in most SQL development environments. The interface has tools for error checking, job history, job status, query history, as well as other things. There is no need to deal with confusing database connection settings as the user's Google login information acts as the connection credentials.
Summary
This chapter covered the basics of using SQL in BigQuery. BigQuery provides a simple and intuitive interface, and the use of the standard SQL dialect provides a level of standardization across other database management systems. The validation features of the interface are not only useful, but are also somewhat rare (at least rare for most integrated development environments for SQL). BigQuery functions are powerful, but are also familiar to any developer with SQL experience. Lastly, most administration tasks can be performed with ease through the use of BigQuery's interface or via the standard SQL language.
Further reading
- Legacy SQL SELECT statement syntax and options: https://cloud.google.com/bigquery/docs/reference/legacy-sql#select-syntax
- Legacy SQL aggregate functions reference: https://cloud.google.com/bigquery/docs/reference/legacy-sql#aggfunctions
- Standard SQL SELECT statement syntax and options: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
- Standard SQL JOINS: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#join-types
- Standard SQL DML statements: https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax
- Standard SQL functions and operators: https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators