Cloud Data-warehousing based on BigQuery

how did we make integration step by step?

1 File uploading to GCS

To implement the Data Warehouse, Google Cloud Platform uses Google Cloud Storage GCS. This is object storage for files of any size and type with cloud access worldwide. In this example, regular import of DZEN statistics data from an advertising account is configured through uploading csv files. The advertising system account does not have an API and allows you to upload only generated files. These files are then loaded via the GCS interface into the bucket.

2 Apps Script

Google apps script is a free service for creating and expanding the functionality of Google applications and more. Allows you to create additional functionality in Java Script languageThe example shows a way to manage price lists of CPA partners. Each price list is created and managed in Google spreadsheet and when you click on the download/button, the corresponding table is created or updated in Bigquery. In this way, calculations of the effectiveness of attraction with changes in the offer are automated.

3 NODE JS SERVER

To implement regular import or streaming of data into Bigquery from local sources, I use Node JS. Since this script has full support for API cloud applications, the necessary libraries and the event loop architecture itself allows you to completely close the issues with parallel loading of information.The Node JS script allows you to replace expensive Pub/Sub + Dataflow services by outsourcing loading and processing to the local machine.In this example, a script is implemented that loads a file into GCS, which can later be automated by emulating KRON or creating emitters for file update and replacement events

4 Cloud console UI

Google Cloud Platform has a very good UI for uploading files and data tables. The screenshots show examples of downloading directly from the browser; the absence of requirements for knowledge of the API or deep immersion in the documentation makes the entry threshold quite low, and the system, in turn, is as convenient as possible. The interface is concise and intuitive. In the case of Bigquery, the work of writing queries can be built exclusively in the UI. However, downloading via the web imposes a number of restrictions, for example on the number of lines or the size of the file for downloading to BigQuery.

5 SAAS Сервисы

When working with a large number of external services, sometimes it makes sense to start working with SAAS solutions, for example, to speed up the start of data collection in BigQuery storageIn my case, I interacted with Owox Pipline to set up cost data transfer from advertising systems.

Page updated

Google Sites