![](\"https:\/\/storage.ning.com\/topology\/rest\/1.0\/file\/get\/9238637677?profile=RESIZE_710x\")
<\/a><\/span><\/h2>\nWhat is Dataproc?<\/span><\/h2>\nDataproc is a low-cost, Google Cloud Platform integrated, easy to use managed Spark and Hadoop service that can be leveraged for batch processing, streaming, and machine learning use cases.<\/span><\/p>\nWhat is Google BigQuery?<\/span><\/h2>\nBigQuery is an enterprise grade data warehouse that enables high-performance SQL queries using the processing power of Google\u2019s infrastructure.<\/span><\/p>\nLoad Data Into Google BigQuery and AutoML<\/span><\/h2>\nUse Case<\/h3>\n
In this blog, we will review ETL data pipeline in StreamSets Transformer,\u00a0<\/span>a Spark ETL engine<\/span><\/a>, to ingest real-world data from Fire Department of New York (FDNY) stored in Google Cloud Storage (GCS), transform it, and store the curated data in Google BigQuery.\u00a0<\/span><\/p>\nOnce the transformed data is made available in Google BigQuery, it will be used in AutoML to train a machine learning model to predict the average incident response time for the FDNY.<\/span><\/p>\nSample Data<\/h3>\n
The dataset is made available through the\u00a0<\/span>NYC Open Data<\/span><\/a>\u00a0website. The 2009-2018 historical dataset contains average response times of the FDNY. The data is partitioned by incident type (False Alarm, Medical Emergency, and so on), borough, and the number of incidents during a particular month.<\/span><\/p>\nHere\u2019s what the sample FDNY data looks like:<\/p>\n
Data Pipeline Overview<\/h3>\n
![](\"https:\/\/storage.ning.com\/topology\/rest\/1.0\/file\/get\/9190941269?profile=RESIZE_710x\")
<\/a><\/p>\nData Source And Dataset<\/b><\/p>\n\n- Data in CSV format is loaded from GCS using Google Cloud Storage (GCS) origin. To load data from GCS, all you need to provide is the path to the bucket, data format, and file name pattern.<\/span><\/li>\n<\/ul>\n
Data Transformations<\/b><\/p>\n\n- \nRaw data is transformed using\u00a0<\/span>Filter<\/span><\/a>,\u00a0<\/span>Field Remover<\/span><\/a>, and\u00a0<\/span>Spark SQL Expression<\/span><\/a>\u00a0processors in a format that is suitable for machine learning. (See details below.)<\/span>\n<\/li>\n<\/ul>\n
Data Storage<\/b><\/p>\n\n- \nTransformed data is stored in a\u00a0<\/span>Google BigQuery<\/span><\/a>\u00a0table.\u00a0<\/span>Note<\/span><\/i>: if the table doesn\u2019t already exist, it will be created automatically by StreamSets Transformer.<\/span>\n<\/li>\n<\/ul>\n
Cluster Type<\/b><\/p>\n\n- \nIn this example, the data pipeline is designed to run on an existing or ephemeral\u00a0<\/span>Google Dataproc<\/span><\/a>\u00a0cluster.\u00a0<\/span>Note<\/span><\/i>: Other supported cluster types in StreamSets Transformer include\u00a0<\/span>Databricks<\/span><\/a>,\u00a0<\/span>Amazon EMR<\/span><\/a>, Azure for HDInsight,\u00a0<\/span>Hadoop YARN<\/span><\/a>, and\u00a0<\/span>SQL Server 2019 Big Data Cluster<\/span><\/a>.<\/span>\n<\/li>\n<\/ul>\n
Data Pipeline Preview<\/span><\/h2>\nBefore running the\u00a0<\/span>Spark ETL<\/span><\/a>\u00a0pipeline in StreamSets Transformer, you can preview the pipeline against the configured Dataproc cluster to examine the data structure, data types, and verify the transformations at every stage. This is also a great way to\u00a0<\/span>debug data pipelines<\/span><\/a>. For more information on pipeline preview, refer to the\u00a0documentation<\/a>.<\/span><\/p>\nFilter<\/span><\/h3>\nUsing a\u00a0<\/span>Filter<\/span><\/a>\u00a0processor we will filter out incidents where\u00a0<\/span>INCIDENTCLASSIFICATION<\/span><\/em>\u00a0== \u201c<\/span><\/i>All Fire\/Emergency Incidents<\/span>\u201c<\/span><\/i>\u00a0or\u00a0<\/span>INCIDENTBOROUGH<\/span><\/em>\u00a0== \u201c<\/span><\/i>Citywide<\/span>\u201d.<\/span><\/i><\/p>\n![](\"https:\/\/storage.ning.com\/topology\/rest\/1.0\/file\/get\/9190942475?profile=RESIZE_710x\")
<\/a><\/p>\nRemove Future Information<\/span><\/h3>\nBecause this is a historical dataset and we\u2019re using it to train a machine learning model, we need to remove information that would not be known at the beginning of the month. In this case, that is\u00a0INCIDENTCOUNT<\/em>.\u00a0<\/i>To remove this field from every record, we\u2019ll use a\u00a0Field Remover<\/a>\u00a0processor.<\/span><\/p>\n![](\"https:\/\/storage.ning.com\/topology\/rest\/1.0\/file\/get\/9190942898?profile=RESIZE_710x\")
<\/a><\/h3>\nData Transformations<\/span><\/h3>\nLabels or target variables in machine learning models are of numeric data type. In this case, the field value of\u00a0<\/span>AVERAGERESPONSETIME<\/span><\/em>\u00a0is transformed in the following steps:<\/span><\/p>\n\n- \nRemove \u201c:\u201d using\u00a0<\/span>Spark SQL<\/span><\/a>\u00a0expression \u2014\u00a0<\/span>replace(AVERAGERESPONSETIME,\u201d:\u201d,\u201d\u201d)<\/em><\/strong>\n<\/li>\n
- Convert from time to seconds and from string datatype to integer using\u00a0<\/span>Spark SQL<\/a>\u00a0<\/span>expression \u2014\u00a0<\/span>round((AVERAGERESPONSETIME \/ 100) * 60 + (AVERAGERESPONSETIME % 100))<\/em><\/strong>\n<\/li>\n<\/ul>\n
![](\"https:\/\/storage.ning.com\/topology\/rest\/1.0\/file\/get\/9190945874?profile=RESIZE_710x\")
<\/a><\/p>\nData Pipeline Execution<\/span><\/h2>\nRunning the\u00a0<\/span>StreamSets Transformer<\/span><\/a>\u00a0data pipeline displays various metrics in real-time. For example, batch processing time taken by each stage as shown below. This is a great way to start looking into fine tuning the processing and transformations.<\/span><\/p>\n![](\"https:\/\/storage.ning.com\/topology\/rest\/1.0\/file\/get\/9190946288?profile=RESIZE_710x\")
<\/a><\/p>\nGoogle BigQuery<\/span><\/h2>\nOnce the pipeline runs successfully, the Google BigQuery table is auto-created, if it doesn\u2019t already exists, and the transformed data is inserted into the table. This dataset is then readily available for querying as shown below.<\/span><\/p>\n![](\"https:\/\/storage.ning.com\/topology\/rest\/1.0\/file\/get\/9190946674?profile=RESIZE_710x\")
<\/a><\/p>\nAutoML<\/span><\/h2>\nThe transformed data stored can then be imported directly from the BigQuery table for training a machine learning model in\u00a0AutoML<\/a>.\u00a0<\/span><\/p>\nUsing AutoML you can build on Google\u2019s machine learning capabilities and create custom machine learning models.<\/p>\n
Import Data<\/span><\/h3>\n![](\"https:\/\/storage.ning.com\/topology\/rest\/1.0\/file\/get\/9190947257?profile=RESIZE_710x\")
<\/a><\/p>\nSelect Target Column<\/span><\/h3>\n![](\"https:\/\/storage.ning.com\/topology\/rest\/1.0\/file\/get\/9190947294?profile=RESIZE_710x\")
<\/a><\/p>\nTrain Machine Learning Model<\/span><\/h3>\n![](\"https:\/\/storage.ning.com\/topology\/rest\/1.0\/file\/get\/9190948055?profile=RESIZE_710x\")
<\/a><\/p>\n<\/div>\n![](\"https:\/\/storage.ning.com\/topology\/rest\/1.0\/file\/get\/9190948463?profile=RESIZE_710x\")
<\/a><\/p>\n<\/p>\nThat\u2019s it! We went from loading raw, real-world data into Google BigQuery to creating a machine learning model in AutoML without any coding or scripting!<\/span><\/p>\nBuild Your Spark ETL and ML Data Pipelines<\/span><\/h2>\nIt goes without saying that training models, evaluating them, model versioning, and serving different versions of the model are non-trivial undertakings and that is not the focus of this post. That said, however, StreamSets Transformer makes it really easy to load data into Google BigQuery and AutoML.<\/span><\/p>\nCheckout these helpful resources and get started quickly with running your\u00a0<\/span>Spark ETL data pipelines<\/span><\/a>.\u00a0<\/span><\/p>\nLearn more about\u00a0<\/span>StreamSets For Google Cloud Platform<\/span><\/a>.<\/span><\/p>\n\u00a0<\/p>\n<\/div>\n
Go to Source<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Dataproc is a low-cost, Google Cloud Platform integrated, easy to use managed Spark and Hadoop service that can be leveraged for batch processing, streaming, and machine learning use cases.<\/span><\/p>\n BigQuery is an enterprise grade data warehouse that enables high-performance SQL queries using the processing power of Google\u2019s infrastructure.<\/span><\/p>\n In this blog, we will review ETL data pipeline in StreamSets Transformer,\u00a0<\/span>a Spark ETL engine<\/span><\/a>, to ingest real-world data from Fire Department of New York (FDNY) stored in Google Cloud Storage (GCS), transform it, and store the curated data in Google BigQuery.\u00a0<\/span><\/p>\n Once the transformed data is made available in Google BigQuery, it will be used in AutoML to train a machine learning model to predict the average incident response time for the FDNY.<\/span><\/p>\n The dataset is made available through the\u00a0<\/span>NYC Open Data<\/span><\/a>\u00a0website. The 2009-2018 historical dataset contains average response times of the FDNY. The data is partitioned by incident type (False Alarm, Medical Emergency, and so on), borough, and the number of incidents during a particular month.<\/span><\/p>\n Here\u2019s what the sample FDNY data looks like:<\/p>\n Data Source And Dataset<\/b><\/p>\n Data Transformations<\/b><\/p>\n Data Storage<\/b><\/p>\n Cluster Type<\/b><\/p>\n Before running the\u00a0<\/span>Spark ETL<\/span><\/a>\u00a0pipeline in StreamSets Transformer, you can preview the pipeline against the configured Dataproc cluster to examine the data structure, data types, and verify the transformations at every stage. This is also a great way to\u00a0<\/span>debug data pipelines<\/span><\/a>. For more information on pipeline preview, refer to the\u00a0documentation<\/a>.<\/span><\/p>\n Using a\u00a0<\/span>Filter<\/span><\/a>\u00a0processor we will filter out incidents where\u00a0<\/span>INCIDENTCLASSIFICATION<\/span><\/em>\u00a0== \u201c<\/span><\/i>All Fire\/Emergency Incidents<\/span>\u201c<\/span><\/i>\u00a0or\u00a0<\/span>INCIDENTBOROUGH<\/span><\/em>\u00a0== \u201c<\/span><\/i>Citywide<\/span>\u201d.<\/span><\/i><\/p>\n Because this is a historical dataset and we\u2019re using it to train a machine learning model, we need to remove information that would not be known at the beginning of the month. In this case, that is\u00a0INCIDENTCOUNT<\/em>.\u00a0<\/i>To remove this field from every record, we\u2019ll use a\u00a0Field Remover<\/a>\u00a0processor.<\/span><\/p>\n Labels or target variables in machine learning models are of numeric data type. In this case, the field value of\u00a0<\/span>AVERAGERESPONSETIME<\/span><\/em>\u00a0is transformed in the following steps:<\/span><\/p>\n Running the\u00a0<\/span>StreamSets Transformer<\/span><\/a>\u00a0data pipeline displays various metrics in real-time. For example, batch processing time taken by each stage as shown below. This is a great way to start looking into fine tuning the processing and transformations.<\/span><\/p>\n Once the pipeline runs successfully, the Google BigQuery table is auto-created, if it doesn\u2019t already exists, and the transformed data is inserted into the table. This dataset is then readily available for querying as shown below.<\/span><\/p>\n The transformed data stored can then be imported directly from the BigQuery table for training a machine learning model in\u00a0AutoML<\/a>.\u00a0<\/span><\/p>\n Using AutoML you can build on Google\u2019s machine learning capabilities and create custom machine learning models.<\/p>\n That\u2019s it! We went from loading raw, real-world data into Google BigQuery to creating a machine learning model in AutoML without any coding or scripting!<\/span><\/p>\n It goes without saying that training models, evaluating them, model versioning, and serving different versions of the model are non-trivial undertakings and that is not the focus of this post. That said, however, StreamSets Transformer makes it really easy to load data into Google BigQuery and AutoML.<\/span><\/p>\n Checkout these helpful resources and get started quickly with running your\u00a0<\/span>Spark ETL data pipelines<\/span><\/a>.\u00a0<\/span><\/p>\n Learn more about\u00a0<\/span>StreamSets For Google Cloud Platform<\/span><\/a>.<\/span><\/p>\n \u00a0<\/p>\n<\/div>\n Go to Source<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"What is Google BigQuery?<\/span><\/h2>\n
Load Data Into Google BigQuery and AutoML<\/span><\/h2>\n
Use Case<\/h3>\n
Sample Data<\/h3>\n
Data Pipeline Overview<\/h3>\n
<\/a><\/p>\n
\n
\n
\n
\n
Data Pipeline Preview<\/span><\/h2>\n
Filter<\/span><\/h3>\n
<\/a><\/p>\n
Remove Future Information<\/span><\/h3>\n
<\/a><\/h3>\n
Data Transformations<\/span><\/h3>\n
\n
<\/a><\/p>\n
Data Pipeline Execution<\/span><\/h2>\n
<\/a><\/p>\n
Google BigQuery<\/span><\/h2>\n
<\/a><\/p>\n
AutoML<\/span><\/h2>\n
Import Data<\/span><\/h3>\n
<\/a><\/p>\n
Select Target Column<\/span><\/h3>\n
<\/a><\/p>\n
Train Machine Learning Model<\/span><\/h3>\n
<\/a><\/p>\n
<\/a><\/p>\n<\/p>\n
Build Your Spark ETL and ML Data Pipelines<\/span><\/h2>\n