- Native tables
- External tables
- Views
- BigQuery's views are logical views, not materialized views, which means that the query that defines the view is re-executed every time the view is queried.
- Views can only reference other tables and views with the same Dataset location.
BigQuery stores data in the Capacitor columnar data format.
Under the hood, analytics throughput is measured in BigQuery slots. A BigQuery slot is a unit of computational capacity required to execute SQL queries. BigQuery automatically calculates how many slots are required by each query, depending on query size and complexity.
To check how many slots your account uses, see Monitoring BigQuery Using Stackdriver.
In BigQuery, you typically should denormalize the data structure in order to enable superfast querying. While JOINs on small datasets are possible with BigQuery, they're not as performant as a denormalized structure.
When dealing with large XML files, it's important to not use a DOM-based parser on the XML file, as the parser will attempt to load the entire file as an in-memory tree.
Instead, we suggest using a pull-based parser or a SAX parser, which are more efficient. Parsing a large XML file is simple when using Python.
Schema automatically generated for Avro files
To enable standard SQL: Click Compose Query -> Click Show Options -> Uncheck the Use Legacy SQL checkbox.
STRING, BYTES, INTEGER, FLOAT, BOOLEAN, RECORD, TIMESTAMP, DATE, TIME, DATETIME
TOP() is equal to GROUP BY...ORDER BY...LIMIT, but the TOP query will execute much faster.
- with
TOP():
SELECT
TOP(word, 10), COUNT(*)
FROM
ds.Table
WHERE
word contains 'th';
- without
TOP():
SELECT
word, COUNT(*) AS cnt
FROM
ds.Table
WHERE
word CONTAINS 'th'
GROUP BY
word
ORDER BY
cnt DESC LIMIT 10;
You can use snapshot decorators to undelete a table within 2 days of table deletion.
bq load --autodetect <destination_table> <data_source_uri>
Enable autodetection of schema and options for formats—such as CSV and JSON—that are not self-describing. The default value is --noautodetect.
It can even automatically convert 'yes|no' to true|false
eg: bq load --autodetect healthcare.business_rules BusinessRules.csv
- Google Analytics Premium can export website data into BigQuery
- Not sure if Google Analytics can import data from BigQuery