BigQuery –匯入資料 part 3

Data Transfer and Exports 這一篇我們繼續來說明BQ的資料轉移與資料匯出,之前篇章我們提到的資料載入都是一次性的工作。但若你的資料是要定期性的匯入資料進入 BQ呢?BQ提供了 “Data transfer Servie” ,讓你定期的自動將資料匯入到 BQ中。這一類的工作BQ提供了Web UI介面/bq command 及REST API的方式來執行這種重複性的工作。BQ的 transfer servicet除了本身就支援Cloud Storage 之外還支援多種資料來源,例如Google Ads,

Read more

BigQuery –匯入資料 part 2

Data Management ( DDL and DML) 這一篇我們來講一下在BQ上的資料管理,例如我們需要刪除資料可以用如下的命令 bq rm dataset_name.table_name 是直接刪除單一個table或是bq rm -f -f dataset_name其中 -r 是 recursively, -f force也就是不管該dataset有什麼資料直接強制刪除而且也不用再跳出確認是否要刪除的訊息。以上是用BQ在cloud

Read more

BigQuery 資料型態與功能 part 2

BigQuery Data types,Functionspart 2 String字串功能 文字的運用應該是在資料庫裡最普遍使用,BQ內建了很多文字運用的語法功能。請看一下範例 上面的範例是將資料計算字元數還有將字串變成小寫,還有尋找在city欄位中”or”這個字是在字串的第幾位,因為Taipei沒有帶”or”的字,故顯示是0.讓我們再看下一個範例 上面範例我們使用的CONCAT的條件式將文字組合起來,先用STRPOS去算email @符號在哪一位置之後用SUBSTR擷取email欄位裡 @ 後的字串。在這裡我們用到了文字字串常用的分離SUBSTR與組合CONCAT。 再來我們看一下字串裡語係的問題,由於BQ是使用Unicode所以它是建立在英語的基礎上的。我們看以下範例 上面的例子我們可以看到,BQ對於每種語係的bytes在Unicode之下的狀況,接下來我們看一下在詳細的字串分析內容 上面的字串的內容分析可以看到每個語言有更多不一樣的地方,所以在處理文字時若遇到語係問題需要特別注意。 另外在BQ中我們還可以針對文字字串在直接做 ETL的處理,我們以下針對以下範例來看BQ在文字的判斷與處理的語法 底下的範例是針對文字做變化處理 再來我們看一下正規表示式 我們以郵遞區號來當作範例,台灣的郵遞區號是3+2,五碼的數字。 從上面的範例正規表示式有幾個部分說明一下1.

Read more

BigQuery 資料型態與功能 part 1

BigQuery Data types,Functions 這一篇我們要來介紹BQ的資料型態與它的功用,BQ支援數字/文字/時間/地理位置/結構化/半結構化等資料型態。底下為大家一一說明 INT64這是一個數字的整數型的資料也就是數字沒帶小數點,範圍可以從10的負19次方到10的19次方。若是real-valued的數字哪麼就需要用FLOAT64的資料型態。 Numeric提供小數點前38位數及小數點之後9個位數的數字型態,這一類的資料型態適合被拿來做計算。若您的資料有需要在資料庫做計算就是適合此類的資料型態。 STRING文字字串,提供不特定長度的文字字串以Unicode為基礎。 TimeStamp一個絕對數字的時間值資料型態 DateTime這是一個日曆的時間型態,Dtae 跟Time可以分開來。 Geography顯示的是經緯度的資料型態 Struct/Array這個在我們前面的BQ SQL基本操作裡有提到並且有範例。 我們來看一個Numeric型態的例子 我們看到這是一個簡單的四則運算,但運算的結果就是有好多小數點的位數。我們這時可以用另一個方式來取小數點後的位數。請參考以下範例。 我們可以用Round 在運算出來的數字後,後面接一個數字來決定我們要取小數點後的幾位數。但有一個特殊狀況,若運算中分母為零的話哪不管什麼數算出來都是零。這時若有這種狀況我們必須再加一個參數上去,請看底下範例。 使用IEEE_Divide這個參數就會針對在運算的數字中特別的資料回傳一個NaN(Not a Number)的值回來。在上面的兩個例子中都是有一到多個數字的input然後回傳一個單一數字。

Read more

BigQuery –Joining Tables (basic operation)

這一篇我們來教學如何在BQ在做基本joining tables的操作,雖然在BQ上是不太鼓勵做joining tables的操作。若是您要分析的資料是從一般的RDBMS的資料庫而來,哪麼建議您在ETL時就將RDBMS的資料做Denormalization的動作,這樣避免掉joining tables的所要的資源及時間後面會有篇章帶到如何在BQ上做joining tables的優化。BQ支援以下的joining tables的型態inner/outer/cross/anti/semi/anti-semi 請看底下的範例 從上面的範例中我們從兩個不同的dataset篩選我們要的資料出來,個別名命為bike_rentals 及rainy_days 。在第三段的select語法將它們做join,其中rainy_days這段的語法中,wx.id = ‘USW00094728’是New York某一個氣象站台。若我們分別執行這兩段bike_rentals 及rainy_days會看到以下的結果 第一段是騎乘數量與日期第二段是日期與時間,最後我們以“日期”為條件將兩個資料合併起來。就變成上面第一個範例的結果這個方式就叫 “Inner Join” from “資料庫A” join

Read more

BigQuery SQL語法基本操作 part 2

上一篇BQ part 1我們介紹了一些簡單的指令,這一篇我們將介紹在BQ上稍微難一些的SQL語法 第一個是平均”AVG”,我們一樣以citibike作為範例底下的語法我們計算了關於男性的騎乘平均的時間。計算tripduration這一個欄位除60之後單位就是分鐘,在針對這個欄位所有的資料做一個平均值其結果是13.4分鐘 select     AVG(tripduration / 60) AS avgtripduration   from     `bigquery-public-data`.new_york_citibike.citibike_trips   where   

Read more

BigQuery SQL語法基本操作 part 1

Big Query CRUD(Create/Read/Update/Delete)的基本操作介紹 BigQuery(以下簡稱BQ)的CRUD操作都可以透過SQL指令來完成 Create: SQL INSERT statementRead: SQL SELETE statementUpdate : SQL UPDATE/MERGE, 雖然Bigquery支援update。但這畢竟是資料分析的 solution。建議還是不要經常做。Delete: SQL DELETE 因為是資料分析的工具,故會用最多的就是”Read”的功能。現行BigQuery 支援SQL:2011的語法。

Read more

BigQuery—-Google Data Warehouse 解決方案

簡介 在現今的企業中,越來越多公司傾向以資料導向作為企業的決策依據。哪麼資料倉儲系統就是一個現代企業不可或缺的一部分。然而現今各種資料來源充斥著整個企業內部,越來越來的資料需要進行分析。傳統上的資料倉儲效能變得越來越糟而企業需要根據資料來做決策卻要越來越即時,因為市場瞬息萬變。而隨著市場的動態,分析的需求也是不固定的。若要準備夠大的資料倉儲系統應對則有資源浪費的狀況發生,資料倉儲太小則分析需求就會有效能太慢跟不及時的狀況發生, BigQuery就在此種狀況狀況下應運而生。 什麼是BigQuery(以下簡稱BQ) Google BQ是一個託管式及高擴充性的服務,也就是說不再需要再像傳統的資料中心一樣建立一堆的機器以及需要各式基礎建設的各類工程師來幫你搭建這個環境,例如在BQ服務下你不需要一個資料庫管理員來做幫你做Data replication/defragmentation/Disaster Recovery等等的工作, Google都會幫你處理這些問題。您只需要一個熟習一般傳統SQL語法的工程師幫你使用即可。Google BQ的效能能夠讓你在一秒內查詢TB及資料量或一分鐘內查詢PB及的資料量,也不再需要建立傳統資料庫為了加快資料量需要建立index機制。BQ除了是一個全託管的服務外,它的高效能也是因為BQ是一種分散式的SQL Engine。你可以使直接在Google的UI介面上使接使用這一項服務,甚至可以查詢的資料不是放在BQ之內,例如 CVS/JSON/Avro等類型的檔案。當然你也可以直接將這些檔案直接載入到BQ內的系統,由於BQ是雲端服務所以你也不用擔心會把BQ的儲存空間用滿,除非你有一天不再需要這些資料。BQ與一般傳統的資料倉儲系統不同在於,一般傳統的資料倉儲系統是 row base的設計,而BQ是用Column base的架構設計(如下圖)。 如何將資料載入BQ呢? 這時免不了的需要ETL這一類的工具拉幫助我們將資料載入到BQ內, GCP也有一套全託管的ETL服務。Extract — Cloud

Read more