BigQuery dataloading with the bq CLI

Sometimes you just want a simple way of importing data into BigQuery without streaming, enrichment or complex transformations. Cloud Dataflow might be too much, and the import through the webpage will not handle large amounts of data, bq load is the CLI way of doing this.

For this demo I'll use demo data from Oslobysykkel.no, which is a bike sharing service here in Oslo. Their data is out in csv and json format at https://developer.oslobysykkel.no/data

I download all the data to my local computer, and now I want to upload it to Google Cloud Storage. I've installed and setup the Google Cloud SDK from here.

Except for the initial upload everything I could've done everything here using the Cloud Shell from within the GCP console, I just like working on my laptop.

mymac:dwn $ gcloud config list
[compute]
region = europe-west3
zone = europe-west3-b
[core]
account = magnus.fagertun@emailprovider.com
disable_usage_reporting = False
project = healthy-basis-19xxxx

Your active configuration is: [default]

Let’s create a bucket in Europe and upload to this bucket, using -m for parallel uploads.

mymac:dwn $ gsutil mb -c regional -l europe-west3 gs://bysykkelbucket
Creating gs://bysykkelbucket/...

Bigquery supports csv data that is gzipped or uncompressed, but these are .zip files, so we will uncompress before uploading. Uncompressed csv is usually faster, and can be read in parallel as well, not that it's needed for these small data volumes.

mymac:dwn $ for i in `ls trips*.csv.zip`;do unzip $i;done
...
Archive:  trips-2017.9.1-2017.9.30.csv.zip
 inflating: trips-2017.9.1-2017.9.30.csv  

mymac:dwn $ gsutil -m cp tri*.csv gs://bysykkelbucket
Copying file://trips-2016.12.1-2016.12.31.csv [Content-Type=text/csv]...
Copying file://trips-2017.9.1-2017.9.30.csv [Content-Type=text/csv]...          
...
| [18/18 files][278.0 MiB/278.0 MiB] 100% Done   8.5 MiB/s ETA 00:00:00
Operation completed over 18 objects/278.0 MiB.        


mymac:dwn $ gsutil ls -lh gs://bysykkelbucket|head -n 2
13.89 MiB  2018-02-19T10:36:28Z  gs://bysykkelbucket/trips-2016.10.1-2016.10.31.csv
 6.18 MiB  2018-02-19T10:36:19Z  gs://bysykkelbucket/trips-2016.11.1-2016.11.30.csv

A quick peek of the data using gsutil cat, selecting only the first 256 bytes.

mymac:dwn $ gsutil cat -r 0-256 gs://bysykkelbucket/trips-2016.11.1-2016.11.30.csv

Start station,Start time,End station,End time
213,2016-11-01 06:00:02 +0100,163,2016-11-01 06:12:24 +0100
283,2016-11-01 06:00:04 +0100,280,2016-11-01 06:07:04 +0100
266,2016-11-01 06:00:11 +0100,196,2016-11-01 06:09:31 +0100

As we can see, the date format is not according to BQ standard, so this needs to be parsed at a later stage.

There are two easy ways to load this data, either loading into BigQuery and then transforming, or using federated queries to query the data where it is in the storage bucket, and then store it to a table in BigQuery.

1. Load into BigQuery, then transform

I will create a new dataset in BigQuery and set this as the default in my shell.

mymac:dwn $ bq mk bysykkel
Dataset 'healthy-basis-19xxxx:bysykkel' successfully created.
mymac:dwn $ echo dataset_id=bysykkel > ~/.bigqueryrc

Now we are ready to load the data. I'll skip the header row, import all into a table named tripsdump. Here I’m specifying the format of the file inline, you can also create a json file with the table definition

mymac:dwn $ bq load --skip_leading_rows=1 tripsdump \ gs://bysykkelbucket/*.csv \ start_station:string,start_time:string,end_station:string,end_time:string
Waiting on bqjob_r21e6f0f984e_000addee25d_1 ... (50s) Current status: DONE  

The data is now loaded into BigQuery, but the data type for dates are not correct. To do this transformation we will use SQL and save the result to a destination table. We could use bq for this as well, but I'll use the web GUI because it is easier to spot typos and errors with the validator.

First we will cast the start and end station as an int ID column, then we will parse the date formats and exclude bad data with nulls.

2. Create an external data source, transform and load into BigQuery with SQL

This method is great for loading large amounts of data, because we load and transform the data in one step. It can also be used to limit the data being loaded with a where clause, or enrich the data from existing tables using SQL.

First we will use the bq mkdef command to create a json table definition.

mymac:dwn $ bq mkdef --noautodetect --source_format=CSV "gs://bysykkelbucket/*.csv" start_station:string,start_time:string,end_station:string,end_time:string > tripsdef.json

Let's open the tripsdef.json and change skipLeadingRows to 1

mymac:dwn $ bq mk --external_table_definition=tripsdef.json bysykkel.trips_ext
Table 'healthy-basis-19xxxx:bysykkel.trips_ext' successfully created.

The external table is now visible in the BigQuery UI, so we run the query we created earlier to clean the data.

If you haven't tried Google Cloud yet, I recommend heading over to cloud.google.com to get started on your free trial.  For further reading on dataloading in BigQuery including newer enhancements like DDL, partitioning and materializing queries, check out this blogpost by @felipehoffa

A few links:
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators

https://cloud.google.com/bigquery/external-table-definition

First 5 months at Google

In January 2018 I left my previous safe home of the Oracle ecosystem and joined Google as a Customer Engineer on Google Cloud Platform. From the moment I started my free trial on GCP, started playing with BigQuery and did things like pressing the "ssh" button in the Cloud UI to get a terminal to my VM from a browser, it felt like I had seen the future.

In my current job I'm for the moment much more of a generalist than I used to be, covering everything from security to devops, data warehousing to serverless development. I can't choose the questions my customers have, and my job is to help them choose the right technologies and make them succeed with their products and their customers. Learning new stuff is fun!

I have never been a very active blogger, and maybe blogging is a bit 2006 anyway. But still, I decided to keep my blog, as a place to write about the stuff that has interested me since I went to school. Processing data, technologies used to store and retrieve data, measuring performance, tips & tricks I discover while exploring Google Cloud Platform and surrounding technologies.

I hope the content here will be of use to someone.

ODC Appreciation Day : AWR

#ThanksODC seems like a good time to knock some rust off my blog.

I would like to direct a thank you to my favorite feature of the Oracle Database, AWR. I did a quick search through my computer for AWR reports, currently I have 720 reports from more than 50 customers, I use this tool all the time. For me, the AWR report is a window into the workload of the database. Here you can see what it is spending most of its time on, if there are any large problems occuring, resource utilization, underlying HW, response time from the disksystem and the list goes on. As long as the customer has the diagnostic license, you can always ask the DBA for an AWR report, it is the easiest way to get detailed information on what the database is doing.

A few use-cases where I think AWR is invaluable:

Sizing, replacement HW and consolidation

An AWR will easily give you information on CPU usage, how fast the current disksystem is and if the database(s) are struggling on current HW. How big will the impact be if I move this database to an Exadata? The Top timed events together with the wait event histogram section will give a clue on how the system will behave with a faster disk system. Also, a graph of CPU usage is good to have in addition to the AWR reports, but the graph will never tell you what the database is spending CPU cycles on.

Troubleshooting, performance problems

This is a given, but often I start asking for an AWR when customers have problems with performance. Is it perfect? No! You only see averages over usually an hour and all info on one session might be hidden. But it's a good starting point for further investigation,.

RAT analysis

For the past year I've been involved with several upgrade project using Real Application Testing when testing their application on 12c. For this we have used RAT to replay the workload from 11g production onto a 12c RAC test database, and for analysis we have used RAT. No automated tools, just plain simple single-node AWR snapshots of 30 minutes. After a few tests you immediately spot when something is off.

So, #ThanksODC for making my life easier with AWR!

Presentation from UKOUG Tech15

Thanks to everyone who attended my presentation at Tech15 on Virtualized Exadata.

Slides are here

It's never too late to upload slides, so here are my slides for my OUGN 2015 presentation on efficient data loading in DWH. Slides in english or norwegian. Script output from live demo is here.

OUGN14 - Multitenant with RAC and Data Guard

Here's the schedule for this years OUGN conference. Lots of interesting stuff here, I'm looking forward to hear Cary Millsap, Kellyn Pot'Vin and the ever present Doug Burns. There will also be some great Norwegian presenters, like Henry Hopkinson from Norgesgruppen on Enterprise Manager. I could continue to list names here, but I can't list all of the 5 tracks.

I'm also presenting again this year, with the title: "Live demo: Multitenant with RAC and Data Guard". I'm co-presenting with my good friend and ex-colleague Håkon Onsager, and we're on right after Cary Millsap on Friday morning. We will look at Multitenant from an operations point of view, and what will it look like in a Data Guard environment with or without RAC. We are also looking into how to get a flexible and stable platform for your future pluggable databases. It will be fun!

Exadata Smart Flash Table Caching

This feature is probably my favorite new feature in 11.2.3.3, because it allows Exadata to do smart scans of segments in flash. The examples below are run on a quarter rack X3-2 HC with storage sw version 12.1.1 and db version 11.2.0.4.

Here I have a simple query joining one small table of 19MB via a hash join with a larger table of ~150GB, with some filtering and a few aggregations.

SQL> explain plan for select /*+ monitoring*/ sum(extended_amt), count(extended_amt) from v_demo 
where sku_item_name like 'Pampers%' and (bsns_unit_key > 2012057 and bsns_unit_key < 2021070)

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 518373028
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                               |     1 |    42 | 69534   (1)| 00:11:36 |       |       |        |      |            |
|   1 |  SORT AGGREGATE                   |                               |     1 |    42 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR                  |                               |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)            | :TQ10001                      |     1 |    42 |            |          |       |       |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE                |                               |     1 |    42 |            |          |       |       |  Q1,01 | PCWP |            |
|*  5 |      HASH JOIN                    |                               |  4668 |   191K| 69534   (1)| 00:11:36 |       |       |  Q1,01 | PCWP |            |
|   6 |       PX RECEIVE                  |                               |     1 |    26 |     6   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   7 |        PX SEND BROADCAST          | :TQ10000                      |     1 |    26 |     6   (0)| 00:00:01 |       |       |  Q1,00 | P->P | BROADCAST  |
|   8 |         PX BLOCK ITERATOR         |                               |     1 |    26 |     6   (0)| 00:00:01 |       |       |  Q1,00 | PCWC |            |
|*  9 |          TABLE ACCESS STORAGE FULL| DWR_SKU_ITEM                  |     1 |    26 |     6   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  10 |       PX BLOCK ITERATOR           |                               |   207M|  3172M| 69515   (1)| 00:11:36 |     1 |   128 |  Q1,01 | PCWC |            |
|* 11 |        TABLE ACCESS STORAGE FULL  | R_DWB_RTL_SLS_RETRN_LINE_ITEM |   207M|  3172M| 69515   (1)| 00:11:36 |     1 |   128 |  Q1,01 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("A"."SKU_ITEM_KEY"="B"."SKU_ITEM_KEY")
   9 - storage("SKU_ITEM_NAME" LIKE 'Pampers%')
       filter("SKU_ITEM_NAME" LIKE 'Pampers%')
  11 - storage("BSNS_UNIT_KEY"<2021070 AND "BSNS_UNIT_KEY">2012057 AND SYS_OP_BLOOM_FILTER(:BF0000,"A"."SKU_ITEM_KEY"))
       filter("BSNS_UNIT_KEY"<2021070 AND "BSNS_UNIT_KEY">2012057 AND SYS_OP_BLOOM_FILTER(:BF0000,"A"."SKU_ITEM_KEY"))

First I'll show an execution from Exadata storage sw version 11.2.3.2.1

SQL> @snap "select /*+ monitoring*/ sum(extended_amt), count(extended_amt) from v_demo 
where sku_item_name like 'Pampers%' and (bsns_unit_key > 2012057 and bsns_unit_key < 2021070)"

SUM(EXTENDED_AMT) COUNT(EXTENDED_AMT)
----------------- -------------------
         47905.43               31471

Elapsed: 00:00:06.27

NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------
-- ExaSnapper v0.7 BETA by Tanel Poder @ Enkitec - The Exadata Experts ( http://www.enkitec.com )
---------------------------------------------------------------------------------------------------------------------------------------------------------

DB_LAYER_IO                    DB_PHYSIO_BYTES               |##################################################|         145469 MB          20708 MB/sec
DB_LAYER_IO                    DB_PHYSRD_BYTES               |##################################################|         145469 MB          20708 MB/sec
DB_LAYER_IO                    DB_PHYSWR_BYTES               |                                                  |              0 MB              0 MB/sec
AVOID_DISK_IO                  PHYRD_FLASH_RD_BYTES          |                                                  |              0 MB              0 MB/sec
AVOID_DISK_IO                  PHYRD_STORIDX_SAVED_BYTES     |#############################################     |         129787 MB          18476 MB/sec
REAL_DISK_IO                   SPIN_DISK_IO_BYTES            |#####                                             |          15682 MB           2232 MB/sec
REAL_DISK_IO                   SPIN_DISK_RD_BYTES            |#####                                             |          15682 MB           2232 MB/sec
REAL_DISK_IO                   SPIN_DISK_WR_BYTES            |                                                  |              0 MB              0 MB/sec
REDUCE_INTERCONNECT            PRED_OFFLOADABLE_BYTES        |##################################################|         145469 MB          20708 MB/sec
REDUCE_INTERCONNECT            TOTAL_IC_BYTES                |                                                  |            577 MB             82 MB/sec
REDUCE_INTERCONNECT            SMART_SCAN_RET_BYTES          |                                                  |            577 MB             82 MB/sec
REDUCE_INTERCONNECT            NON_SMART_SCAN_BYTES          |                                                  |              0 MB              0 MB/sec
CELL_PROC_DEPTH                CELL_PROC_DATA_BYTES          |#####                                             |          15685 MB           2233 MB/sec
CELL_PROC_DEPTH                CELL_PROC_INDEX_BYTES         |                                                  |              0 MB              0 MB/sec
CLIENT_COMMUNICATION           NET_TO_CLIENT_BYTES           |                                                  |              0 MB              0 MB/sec
CLIENT_COMMUNICATION           NET_FROM_CLIENT_BYTES         |                                                  |              0 MB              0 MB/sec

Here I'm using Tanel Poders Exasnapper to show a detailed view of where the I/O is done. In total the database should read ~145GB. 129GB is saved through the storage indexes and 15GB is read from the traditional HDD in the Exadata. The last column with MB/s is pretty correct here for the HDDs, we are reading 2232MB/s. A quarter rack is able to achieve faster throughput from disk, the reason why this is not higher is probably because it is not completely sequential I/O due to the storage indexes.

Next I'm running the same query on storage sw version 12.1.1.1

SQL> @snap "select /*+ monitoring*/ sum(extended_amt), count(extended_amt) from v_demo 
where sku_item_name like 'Pampers%' and (bsns_unit_key > 2012057 and bsns_unit_key < 2021070)";

SUM(EXTENDED_AMT) COUNT(EXTENDED_AMT)
----------------- -------------------
         47905.43               31471

Elapsed: 00:00:01.69

NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------
-- ExaSnapper v0.7 BETA by Tanel Poder @ Enkitec - The Exadata Experts ( http://www.enkitec.com )
---------------------------------------------------------------------------------------------------------------------------------------------------------

DB_LAYER_IO                    DB_PHYSIO_BYTES               |##################################################|         145469 MB          67812 MB/sec
DB_LAYER_IO                    DB_PHYSRD_BYTES               |##################################################|         145469 MB          67812 MB/sec
DB_LAYER_IO                    DB_PHYSWR_BYTES               |                                                  |              0 MB              0 MB/sec
AVOID_DISK_IO                  PHYRD_FLASH_RD_BYTES          |#####                                             |          15212 MB           7091 MB/sec
AVOID_DISK_IO                  PHYRD_STORIDX_SAVED_BYTES     |#############################################     |         129790 MB          60503 MB/sec
REAL_DISK_IO                   SPIN_DISK_IO_BYTES            |                                                  |            467 MB            218 MB/sec
REAL_DISK_IO                   SPIN_DISK_RD_BYTES            |                                                  |            467 MB            218 MB/sec
REAL_DISK_IO                   SPIN_DISK_WR_BYTES            |                                                  |              0 MB              0 MB/sec
REDUCE_INTERCONNECT            PRED_OFFLOADABLE_BYTES        |##################################################|         145469 MB          67812 MB/sec
REDUCE_INTERCONNECT            TOTAL_IC_BYTES                |#                                                 |           1742 MB            812 MB/sec
REDUCE_INTERCONNECT            SMART_SCAN_RET_BYTES          |#                                                 |           1742 MB            812 MB/sec
REDUCE_INTERCONNECT            NON_SMART_SCAN_BYTES          |                                                  |              0 MB              0 MB/sec
CELL_PROC_DEPTH                CELL_PROC_DATA_BYTES          |#####                                             |          14523 MB           6770 MB/sec
CELL_PROC_DEPTH                CELL_PROC_INDEX_BYTES         |                                                  |              0 MB              0 MB/sec
CLIENT_COMMUNICATION           NET_TO_CLIENT_BYTES           |                                                  |              0 MB              0 MB/sec
CLIENT_COMMUNICATION           NET_FROM_CLIENT_BYTES         |                                                  |              0 MB              0 MB/sec

Elapsed time dropped from 6,27 seconds to 1,69 second, and now all I/O is done on flash as we can see from PHYRD_FLASH_RD_BYTES.

Earlier I had to use the alter table x storage(cell_flash_cache keep) to get smart scans to run from flash, now it just works! I have no experience with this in a real production system, if anyone has any real world experience with this please leave a comment!

Enabling flash cache compression on Exadata X3-2

I have a plan to write a few posts about the new features in the storage software that was released in December 2013, first out is this one on enabling flash cache compression.

What you need to enable this feature

• An Oracle Exadata of generation X3 or X4
• Storage software 11.2.3.3 or 12.1.
• Licenses for the Advanced Compression Option

Flash cache compression will allow the storage cells to compress and decompress data in the flash cards on the fly. The compression is done in the flash cards themselves, so it does not use CPU on the cell hosts and it is completely transparent to the database.

This procedure is explained in the release notes for the Storage cell software, for 11.2.3.3 that is doc id 1487339.1. I'm doing this with downtime, so all databases are down and the GI is down. See MOS if you want to do this rolling with the databases and GI up.

To enable the compression we need to drop the flash cache, the area reserved for smart flash logging and then all flashdisks. This is done as root on the first cellserver

[root@dm01cel01 ~]# cellcli -e alter flashcache all flush
Flash cache dm01cel01_FLASHCACHE altered successfully
[root@dm01cel01 ~]# cellcli -e drop flashcache all
Flash cache dm01cel01_FLASHCACHE successfully dropped
[root@dm01cel01 ~]# cellcli -e drop flashlog all
Flash log dm01cel01_FLASHLOG successfully dropped
[root@dm01cel01 ~]# cellcli -e drop celldisk all flashdisk
CellDisk FD_00_dm01cel01 successfully dropped
CellDisk FD_01_dm01cel01 successfully dropped
...

Because this is an X3-2 system we must enable support for compression and then enable the compression.

[root@dm01cel01 ~]# cellcli -e alter cell "flashCacheCompX3Support=TRUE"
Cell dm01cel01 successfully altered
[root@dm01cel01 ~]# cellcli -e "alter cell flashCacheCompress=TRUE"
Cell dm01cel01 successfully altered

Recreating celldisks, then the cache for smart flash logging and finally the flash cache itself.

[root@dm01cel01 ~]# cellcli -e "create celldisk all flashdisk"
CellDisk FD_00_dm01cel01 successfully created
CellDisk FD_01_dm01cel01 successfully created
...
[root@dm01cel01 ~]# cellcli -e "create flashlog all size=512M"
Flash log dm01cel01_FLASHLOG successfully created
[root@dm01cel01 ~]# cellcli -e create flashcache all
Flash cache dm01cel01_FLASHCACHE successfully created

This needs to be repeated on all storage cells. That's it! On a quarter rack this was done within half an hour.

[root@dm01db01 ~]#  dcli -g ~/cell_group -l root cellcli -e "list cell detail"|grep flash
dm01cel01: flashCacheMode:       WriteBack
dm01cel01: flashCacheCompress:   TRUE
dm01cel02: flashCacheMode:       writeback
dm01cel02: flashCacheCompress:   TRUE
dm01cel03: flashCacheMode:       writeback
dm01cel03: flashCacheCompress:   TRUE

Update:

@db_magnus Works fabulous on the X4-2 too. Did you notice that the flash cache doubled in size after enabling compression?
— Carol Dacko (@cdacko) February 10, 2014

Doubling of cache size? Sure thing! I didn't check this before the change, so I had to double-check the X3 datasheet, but it should have 1,6TB of flash pr storage cell.

[root@dm01cel01 ~]# cellcli -e list flashcache detail|grep -i Size
         effectiveCacheSize:     2978.75G
         size:                   2978.75G