<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Daniel De Leo on Medium]]></title>
        <description><![CDATA[Stories by Daniel De Leo on Medium]]></description>
        <link>https://medium.com/@danielrdeleo?source=rss-c183f06998a1------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*MfvMWH0vdVXU9je34THC0w.png</url>
            <title>Stories by Daniel De Leo on Medium</title>
            <link>https://medium.com/@danielrdeleo?source=rss-c183f06998a1------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 09:07:09 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@danielrdeleo/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Coding Apache Beam in your Web Browser and Running it in Cloud Dataflow]]></title>
            <link>https://medium.com/google-cloud/coding-apache-beam-in-your-web-browser-and-running-it-in-cloud-dataflow-c41c275d42c8?source=rss-c183f06998a1------2</link>
            <guid isPermaLink="false">https://medium.com/p/c41c275d42c8</guid>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[code-editor]]></category>
            <category><![CDATA[google-cloud-dataflow]]></category>
            <category><![CDATA[apache-beam]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Daniel De Leo]]></dc:creator>
            <pubDate>Thu, 26 Jul 2018 23:06:51 GMT</pubDate>
            <atom:updated>2019-03-26T20:40:30.696Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Update: Code Editor is now based on </strong><a href="http://theia-ide.org/"><strong>Eclipse’s Theia</strong></a><strong>, not </strong><a href="https://github.com/eclipse/orion.client"><strong>Orion</strong></a><strong>. Screenshots and Editor Settings will look slightly different in this post (as they’re based on Orion) but all commands/instructions work the same.</strong></p><h3>Your web browser is hiding a secret</h3><p>Most developers have an <a href="https://wikipedia.org/wiki/Integrated_development_environment">IDE</a> (Integrated Development Environment) of choice for developing Apache Beam code, which they install locally on their laptops/desktops (like <a href="https://medium.com/google-cloud/python-development-environments-for-apache-beam-on-google-cloud-platform-b6f276b344df"><strong>PyCharm for Python</strong></a> or <a href="https://medium.com/google-cloud/setting-up-a-java-development-environment-for-apache-beam-on-google-cloud-platform-ec0c6c9fbb39"><strong>IntelliJ for Java</strong></a>). But what happens when you’re on a computer which doesn’t support your IDE of choice, or you’re using someone else’s computer? Google has you covered! Google’s <a href="https://cloud.google.com/shell/docs/features">Cloud Shell</a> comes with a built-in <a href="https://console.cloud.google.com/cloudshell/editor">Code Editor</a> for developing/modifying code (it’s based on <a href="https://github.com/eclipse/orion.client">Eclipse’s Orion</a>). It’s not as full featured as an IDE but it does beat using Vim or Emacs to edit code! Yes, yes, I know these are fighting words for some people, but I’m speaking to those who agree with me (aka the majority 😜).</p><p>✌️ <em>Here’s a</em> <em>peace offering tip to Vim and Emacs users, Code Editor supports your key bindings via a change in </em><strong><em>Settings &gt; Editor Settings &gt; Keys &gt; Key Bindings.</em></strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0erWYTCyut24eu1C_g3gBg.png" /><figcaption>Code Editor (shown in dark theme)</figcaption></figure><h4>🔆 😎 🔆 If you don’t like bright light beaming at your face, then switch Code Editor to the dark theme</h4><p>Just go to the code editor settings and change both the <strong>Editor Theme </strong>and the <strong>IDE Theme</strong> categories to Dark theme (shown in gif below).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uLQEbvFPIfV9dfVHFUKUQw.gif" /><figcaption>Setting Code Editor to Dark Theme</figcaption></figure><h3>So how do I use the <a href="https://console.cloud.google.com/cloudshell/editor">Code Editor</a> with Cloud Dataflow?</h3><p>Glad you asked! To keep things simple, you’re not going create an Apache Beam pipeline from scratch. You’ll instead use Code Editor to modify one of the <a href="https://github.com/GoogleCloudPlatform/DataflowTemplates">open source Dataflow templates</a> that Google provides for your convenience. Specifically, you’ll modify the Cloud Pub/Sub to BigQuery template (<a href="https://github.com/GoogleCloudPlatform/DataflowTemplates/blob/master/src/main/java/com/google/cloud/teleport/templates/PubSubToBigQuery.java">PubSubToBigQuery.java</a>) to take a Pub/Sub subscription as an input instead of a Pub/Sub topic.</p><h4>Summary of what you’ll do:</h4><ul><li>Clone the GitHub DataflowTemplates repo to your Cloud Shell <strong>$HOME </strong>directory *<strong>(see note below)</strong></li><li>Modify the Cloud Pub/Sub to BigQuery template using Code Editor</li><li>Build the modified template using Maven and stage it in Cloud Storage</li><li>Run a job in Cloud Dataflow using your custom built template</li></ul><p><strong>*Note: </strong>Since Code Editor is a feature built into Cloud Shell, any code that you want to modify in Code Editor must first be present in Cloud Shell’s <a href="https://cloud.google.com/shell/docs/features#persistent_disk_storage">free 5 GB persistent disk storage</a> (this is mounted as your <strong>$HOME</strong> directory). If you do not access Cloud Shell regularly, the <strong>$HOME</strong> directory persistent storage may be recycled. You will receive an email notification before this occurs. Starting a Cloud Shell session will prevent its removal.</p><h3>Setting up Code Editor</h3><h4>1. Open the <a href="https://console.cloud.google.com/cloudshell/editor">Code Editor</a></h4><p>You can use the <a href="https://console.cloud.google.com/cloudshell/editor">direct link</a> to navigate to a page where the upper half is the code editor, and the lower half is the cloud shell. You’ll use the shell to execute all the commands in the following steps.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*u4QzRUqD_G-pey54o75cYQ.png" /><figcaption>Cloud Shell / Code Editor</figcaption></figure><p>You can also open the Code Editor directly from any cloud shell instance.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/897/1*8D2RyKJtPTzdPgje9BX0ZA.png" /><figcaption>Cloud Shell’s Editor Button</figcaption></figure><h4>2. <em>Enable the necessary APIs for this example </em>and clone the <a href="https://github.com/GoogleCloudPlatform/DataflowTemplates">GitHub DataflowTemplates</a> repo in your cloud shell home directory</h4><ul><li><a href="https://console.cloud.google.com/flows/enableapi?apiid=dataflow,compute_component,logging,storage_component,storage_api,bigquery,pubsub"><strong>Click this helper link</strong></a> to bulk enable all necessary APIs: Dataflow — Compute Engine — Logging — Cloud Storage — Cloud Storage JSON — BigQuery — PubSub</li><li><a href="https://console.cloud.google.com/cloudshell/open?git_repo=https%3A%2F%2Fgithub.com%2FGoogleCloudPlatform%2FDataflowTemplates&amp;page=shell"><strong>Click this helper link</strong></a> to auto-clone the GitHub DataflowTemplates repo in your cloud shell home directory</li></ul><pre># Set env variables<br>gcloud config set project <strong>[YOUR_PROJECT_ID]</strong></pre><pre># Clone DataflowTemplates repo<br>cd ~ &amp;&amp; git clone <a href="https://github.com/GoogleCloudPlatform/DataflowTemplates.git">https://github.com/GoogleCloudPlatform/DataflowTemplates.git</a></pre><p><em>🔑 Key Point❗️ : Make sure you enable the APIs in the same project that you use in the “gcloud config set project” command.</em></p><h4>3. Open the PubSubToBigQuery.java file in the <a href="https://console.cloud.google.com/cloudshell/editor">Code Editor</a></h4><p>You’ll edit this file in the next section</p><pre>cloudshell edit ~/DataflowTemplates/src/main/java/com/google/cloud/teleport/templates/PubSubToBigQuery.java</pre><h3>Modifying the Cloud Pub/Sub to BigQuery template</h3><h4>1. First change the input value provider variable from topic to subscription</h4><p>(changes shown in bold below)</p><p>Replace lines 141–144:</p><pre><a href="http://twitter.com/Description">@Description</a>(&quot;Pub/Sub <strong>topic</strong> to read the input from&quot;)<br>ValueProvider&lt;String&gt; <strong>getInputTopic</strong>();</pre><pre>void <strong>setInputTopic</strong>(ValueProvider&lt;String&gt; value);</pre><p>With:</p><pre>// Modified Template input parameter<a href="http://twitter.com/Description"><br>@Description</a>(&quot;Pub/Sub <strong>subscription</strong> to read the input from&quot;)<br>ValueProvider&lt;String&gt; <strong>getInputSubscription</strong>();<br>void <strong>setInputSubscription</strong>(ValueProvider&lt;String&gt; value);</pre><h4>2. And then change its usage in the pipeline’s first apply method</h4><p>(changes shown in bold below)</p><p>Replace lines 202–204:</p><pre>.apply(&quot;ReadPubsubMessages&quot;,PubsubIO.readMessagesWithAttributes().<strong>fromTopic</strong>(options.<strong>getInputTopic</strong>()))</pre><p>With:</p><pre>.apply(&quot;ReadPubsubMessages&quot;,PubsubIO.readMessagesWithAttributes().<strong>fromSubscription</strong>(options.<strong>getInputSubscription</strong>()))</pre><h3>Building and Running your modified Cloud Pub/Sub to BigQuery template</h3><h4>1. Create a Cloud Storage Bucket for staging and running the Dataflow template</h4><p>You’re creating a <a href="https://cloud.google.com/storage/docs/storage-classes#regional">regional</a> bucket to gain better performance for data-intensive computations which are common in Dataflow pipelines.</p><pre>BUCKET_NAME=gs://<strong>[YOUR_STORAGE_BUCKET]</strong><br>gsutil mb -c regional -l us-central1 $BUCKET_NAME</pre><p><strong>Note:</strong> If not specified, us-central1 is the default region for Dataflow jobs. For best performance and to avoid network egress charges, keep your Dataflow jobs in the same region as the data being processed.</p><h4>2. Use Cloud Shell’s built-in Maven tool to build the PubSubToBigQuery.java code which in turn also creates and stores a Dataflow template file in Google Cloud Storage</h4><p><strong>Note:</strong> The resulting Dataflow template file is stored in the location specified by the --templateLocation flag.</p><pre>cd ~/DataflowTemplates &amp;&amp; mvn compile exec:java \<br>-Dexec.mainClass=com.google.cloud.teleport.templates.PubSubToBigQuery \<br>-Dexec.cleanupDaemonThreads=false \<br>-Dexec.args=&quot; \<br>--project=${GOOGLE_CLOUD_PROJECT} \<br>--stagingLocation=${BUCKET_NAME}/staging \<br>--tempLocation=${BUCKET_NAME}/temp \<br>--templateLocation=${BUCKET_NAME}/template \<br>--runner=DataflowRunner&quot;</pre><h4>3. Create a Pub/Sub subscription and BigQuery dataset to use with your newly modified Dataflow template</h4><pre><a href="https://cloud.google.com/sdk/gcloud/reference/pubsub/topics/create">gcloud pubsub topics create</a> test_topic &amp;&amp; \</pre><pre>SUBSCRIPTION=$(<a href="https://cloud.google.com/sdk/gcloud/reference/pubsub/subscriptions/create">gcloud pubsub subscriptions create</a> test_subscription --topic=test_topic --format=&quot;value(name)&quot;) &amp;&amp; \</pre><pre>bq mk pubsub_to_bigquery_dataset</pre><h4>4. Lastly, run a Dataflow job using the template file that you created in step 2</h4><p>You can view your streaming job in the <a href="https://console.cloud.google.com/dataflow">Dataflow page</a></p><pre># Job Names must be unique for every run<br>JOB_NAME=pubsub-to-bigquery-$USER-`date +&quot;%Y%m%d-%H%M%S%z&quot;` &amp;&amp; \</pre><pre># Run Dataflow job and store job id for easy cleanup after<br>JOB_ID=$( \<br>gcloud dataflow jobs run ${JOB_NAME} \<br>--gcs-location=${BUCKET_NAME}/template \<br>--parameters \<br>&quot;inputSubscription=${SUBSCRIPTION},outputTableSpec=${GOOGLE_CLOUD_PROJECT}:pubsub_to_bigquery_dataset.pubsub_to_bigquery_output,outputDeadletterTable=${GOOGLE_CLOUD_PROJECT}:pubsub_to_bigquery_dataset.pubsub_to_bigquery_deadletter&quot; \<br>--format=&quot;value(id)&quot;)</pre><h3>Cleaning up to avoid recurring billing!</h3><pre>gcloud dataflow jobs cancel $JOB_ID<br>gcloud pubsub subscriptions delete $SUBSCRIPTION<br>gcloud pubsub topics delete test_topic<br>bq rm -r -f -d $GOOGLE_CLOUD_PROJECT:pubsub_to_bigquery_dataset</pre><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fgiphy.com%2Fembed%2FhmGQKkNaUIgHS%2Ftwitter%2Fiframe&amp;display_name=Giphy&amp;url=https%3A%2F%2Fmedia.giphy.com%2Fmedia%2FhmGQKkNaUIgHS%2Fgiphy.gif&amp;image=https%3A%2F%2Fi.giphy.com%2Fmedia%2FhmGQKkNaUIgHS%2Fgiphy-downsized-large.gif&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=giphy" width="435" height="435" frameborder="0" scrolling="no"><a href="https://medium.com/media/eb4e5a03c9d139ffa5278a27764f7485/href">https://medium.com/media/eb4e5a03c9d139ffa5278a27764f7485/href</a></iframe><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c41c275d42c8" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/coding-apache-beam-in-your-web-browser-and-running-it-in-cloud-dataflow-c41c275d42c8">Coding Apache Beam in your Web Browser and Running it in Cloud Dataflow</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Moving Data with Apache Sqoop in Google Cloud Dataproc]]></title>
            <link>https://medium.com/google-cloud/moving-data-with-apache-sqoop-in-google-cloud-dataproc-4056b8fa2600?source=rss-c183f06998a1------2</link>
            <guid isPermaLink="false">https://medium.com/p/4056b8fa2600</guid>
            <category><![CDATA[hadoop]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[apache-sqoop]]></category>
            <category><![CDATA[google-cloud-dataproc]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Daniel De Leo]]></dc:creator>
            <pubDate>Fri, 01 Jun 2018 19:46:24 GMT</pubDate>
            <atom:updated>2020-10-01T20:19:13.359Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/641/1*sd3yiSAX6WVnroTeBEN6ag.jpeg" /></figure><p>Have a relational database? — Want to use <a href="https://sqoop.apache.org/">Apache Sqoop</a> to export this database into <a href="https://cloud.google.com/storage/">Cloud Storage</a>, <a href="https://cloud.google.com/bigquery/">BigQuery</a>, or <a href="https://hive.apache.org/">Apache Hive</a>? — Want to do this all via <a href="https://cloud.google.com/dataproc/">Cloud Dataproc</a> so you only pay for what you use?</p><p>Hope you answered <strong>YES</strong> because that’s what this post is about!</p><p>Cloud Dataproc is awesome because it quickly creates a Hadoop cluster which you can then use to run your Hadoop jobs (specifically Sqoop job in this post), and then as soon as your jobs finish you can <strong>immediately</strong> delete the cluster. This is a great example of leveraging Dataproc’s ephemeral and pay-per-use model to cut costs since now you can quickly create/delete hadoop clusters, and never again leave a cluster running idle!</p><p>Here’s the quick scoop on using Sqoop (see what I did there 😏) to move data:</p><ul><li>Sqoop imports data from a relational database system or a mainframe into HDFS (Hadoop Distributed File System).</li><li>Running Sqoop on a Dataproc Hadoop cluster gives you access to the built-in Cloud Storage connector which lets you use the Cloud Storage gs:// file prefix instead of the Hadoop hdfs:// file prefix.</li><li>The two previous points mean you can use Sqoop to import data directly into Cloud Storage, skipping HDFS altogether!</li><li>Once your data is in Cloud Storage you can simply <a href="https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-avro#bigquery-import-gcs-file-cli">load the data</a> into BigQuery using the Cloud SDK <a href="https://cloud.google.com/bigquery/docs/bq-command-line-tool">bq command-line tool</a>. <strong>Alternatively, you can have Sqoop import data directly into your Dataproc cluster’s Hive warehouse which can be based on Cloud Storage instead of HDFS by pointing </strong><em>hive.metastore.warehouse.dir </em><strong>to a </strong><a href="https://cloud.google.com/storage/docs/creating-buckets"><strong>GCS bucket</strong></a><strong>.</strong></li></ul><p>You can use two different methods to submit Dataproc jobs to a cluster:</p><h4><strong>Method 1.) Manual Dataproc Job Submission</strong></h4><ul><li><a href="https://cloud.google.com/dataproc/docs/guides/create-cluster">Create Dataproc cluster</a></li><li><a href="https://cloud.google.com/dataproc/docs/guides/submit-job">Submit Dataproc job(s)</a></li><li><a href="https://cloud.google.com/dataproc/docs/guides/manage-cluster#deleting_a_cluster">Delete Dataproc cluster when job(s) complete</a></li></ul><h4><strong>Method 2.) Automated Dataproc Job Submission using Workflow Templates</strong></h4><ul><li>Create a <a href="https://cloud.google.com/dataproc/docs/concepts/workflows/overview">workflow template</a> which automatically executes the 3 previous manual steps (create, submit, delete).</li></ul><p>It’s easier to troubleshoot job errors using the manual job submission method because you control when to delete the cluster. The automated method, using workflow templates, is ideal once you’re ready to run jobs in production since it takes care of the cluster creation, job submission, and deletion. Both methods are actually very similar to set up and are covered in more detail below.</p><h3><strong>Wait! Before you read on…</strong></h3><p>The examples below demonstrate using Sqoop to connect to a MySQL database. You can quickly and easily create your own test MySQL database in GCP by following the online <a href="https://cloud.google.com/sql/docs/mysql/quickstart">Quickstart for Cloud SQL for MySQL</a>. If you complete the quickstart, don’t forget to create the Dataproc cluster with the <a href="https://github.com/GoogleCloudPlatform/dataproc-initialization-actions/tree/master/cloud-sql-proxy">Cloud SQL Proxy Initialization Action</a> so that you can easily connect to the MySQL database. Lastly, avoid incurring charges by <a href="https://cloud.google.com/sql/docs/mysql/quickstart#clean-up">cleaning up</a> the database when you’re done.</p><h3>3 Steps for Manual Dataproc Job Submission</h3><p><strong>1. Create a Dataproc cluster</strong></p><p>The gcloud tool’s <a href="https://cloud.google.com/dataproc/docs/guides/create-cluster">Dataproc cluster create command</a> will by default create one master node VM (Virtual Machine) and two worker node VMs. All 3 node VMs are type n1-standard-4 machine. The example commands below use the minimum necessary flags for simplicity. <strong>Note: The last </strong>--properties<strong> flag is used in order to overwrite the default on-cluster Hive warehouse directory (hdfs:///user/hive/warehouse) with a Cloud Storage location </strong>gs://&lt;GCS_BUCKET&gt;/hive-warehouse<strong>. You persist all your Hive data (even when you delete the Dataproc cluster) by pointing this Hive warehouse directory </strong>hive.metastore.warehouse.dir<strong> to Cloud Storage.</strong></p><ul><li><em>Create your cluster using the command below if you’re connecting to a MySQL database hosted on-prem and therefore don’t need </em><a href="https://cloud.google.com/sql/docs/mysql/sql-proxy"><em>Cloud SQL Proxy</em></a><em>. Make sure you assign the cluster to the </em><a href="https://cloud.google.com/vpc/"><em>VPC network</em></a><em> that’s shared between your GCP environment and your on-prem environment.</em></li></ul><pre>gcloud dataproc clusters create &lt;<strong>CLUSTER_NAME</strong>&gt; --zone=&lt;<strong>ZONE</strong>&gt; --network=&lt;<strong>VPC_NETWORK</strong>&gt; --properties=hive:hive.metastore.warehouse.dir=gs://&lt;<strong>GCS_BUCKET</strong>&gt;/hive-warehouse</pre><ul><li><em>Create your cluster using the command below if you’re connecting to a Cloud SQL-MySQL database using </em><a href="https://cloud.google.com/sql/docs/mysql/sql-proxy"><em>Cloud SQL Proxy</em></a><em>. </em><strong><em>Note: Since the default MySQL port 3306 is already occupied by Dataproc’s default Hive metadata MySQL server, you need to choose a value other than 3306 for &lt;PORT&gt; (e.g. 3307).</em></strong></li></ul><pre>gcloud dataproc clusters create &lt;<strong>CLUSTER_NAME</strong>&gt; --zone=&lt;<strong>ZONE</strong>&gt; --scopes=default,sql-admin --initialization-actions=gs://dataproc-initialization-actions/cloud-sql-proxy/cloud-sql-proxy.sh --properties=hive:hive.metastore.warehouse.dir=gs://&lt;<strong>GCS_BUCKET</strong>&gt;/hive-warehouse --metadata=enable-cloud-sql-hive-metastore=false --metadata=additional-cloud-sql-instances=&lt;<strong>PROJECT_ID</strong>&gt;:&lt;<strong>REGION</strong>&gt;:&lt;<a href="https://cloud.google.com/sql/docs/mysql/instance-info"><strong>SQL_INSTANCE_NAME</strong></a>&gt;=tcp:&lt;<strong>PORT</strong>&gt; </pre><p><strong>2. Submit a dataproc hadoop job which runs the </strong><a href="https://sqoop.apache.org/docs/1.4.7/SqoopUserGuide.html#_literal_sqoop_import_literal"><strong>Sqoop import tool</strong></a></p><ul><li><em>Submit the job command below if you want Sqoop to import your database table as Avro files into Cloud Storage, which you’ll then </em><a href="https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-avro#bigquery-import-gcs-file-cli"><em>load into BigQuery</em></a><em> using the bq tool.</em></li></ul><pre>gcloud dataproc jobs submit hadoop --cluster=&lt;<strong>CLUSTER_NAME</strong>&gt; --class=org.apache.sqoop.Sqoop --jars=gs://&lt;<strong>GCS_BUCKET</strong>&gt;/sqoop-1.4.7-hadoop260.jar,gs://&lt;<strong>GCS_BUCKET</strong>&gt;/avro-tools-1.8.2.jar,file:///usr/share/java/mysql-connector-java-5.1.42.jar -- import -Dmapreduce.job.user.classpath.first=true --connect=jdbc:mysql://&lt;<strong>HOSTNAME</strong>&gt;:&lt;<strong>PORT</strong>&gt;/&lt;<strong>DATABASE_NAME</strong>&gt; --username=&lt;<strong>DATABASE_USERNAME</strong>&gt; --password-file=gs://&lt;<strong>GCS_BUCKET</strong>&gt;/passwordFile.txt --target-dir=gs://&lt;<strong>GCS_BUCKET</strong>&gt;/mysql_output --table=&lt;<strong>TABLE</strong>&gt; --as-avrodatafile</pre><ul><li><em>Submit the job command below if you want Sqoop to import your database table into a Hive table. </em><strong><em>Note: The command below is the same as above except it adds one more jar </em></strong>file:///usr/lib/hive/lib/hive-exec.jar<strong><em> to the </em></strong>— -jars=<strong><em> argument, replaces </em></strong>--as-avrodatafile<strong><em> with </em></strong>--hive-import , and removes the --target-dir flag.</li></ul><pre>gcloud dataproc jobs submit hadoop --cluster=&lt;<strong>CLUSTER_NAME</strong>&gt; --class=org.apache.sqoop.Sqoop --jars=gs://&lt;<strong>GCS_BUCKET</strong>&gt;/sqoop-1.4.7-hadoop260.jar,gs://&lt;<strong>GCS_BUCKET</strong>&gt;/avro-tools-1.8.2.jar,file:///usr/share/java/mysql-connector-java-5.1.42.jar,file:///usr/lib/hive/lib/hive-exec.jar -- import -Dmapreduce.job.user.classpath.first=true --connect=jdbc:mysql://&lt;<strong>HOSTNAME</strong>&gt;:&lt;<strong>PORT</strong>&gt;/&lt;<strong>DATABASE_NAME</strong>&gt; --username=&lt;<strong>DATABASE_USERNAME</strong>&gt; --password-file=gs://&lt;<strong>GCS_BUCKET</strong>&gt;/passwordFile.txt --table=&lt;<strong>TABLE</strong>&gt; --hive-import</pre><p><strong>3. Delete the cluster once the Dataproc job completes</strong></p><pre>gcloud dataproc clusters delete &lt;<strong>CLUSTER_NAME</strong>&gt;</pre><h3>Take a Closer Look at Step 2</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/640/1*4JZ7IhpOBzsTm35Fbdlqwg.jpeg" /><figcaption>Closer Look</figcaption></figure><p>gcloud dataproc jobs submit hadoop←This first section of the command is where you invoke the <a href="https://cloud.google.com/sdk/gcloud/reference">gcloud tool</a>. The gcloud tool has A LOT of functions, so more specifically, you’re calling the <a href="https://cloud.google.com/sdk/gcloud/reference/dataproc/jobs/submit/hadoop">hadoop</a> job submission command. This command is nested under several layers of command groups in the gcloud tool: <a href="https://cloud.google.com/sdk/gcloud/reference/beta/dataproc">dataproc group</a> -&gt; <a href="https://cloud.google.com/sdk/gcloud/reference/dataproc/jobs">job group</a> -&gt; <a href="https://cloud.google.com/sdk/gcloud/reference/dataproc/jobs/submit">submit group</a> -&gt; <a href="https://cloud.google.com/sdk/gcloud/reference/dataproc/jobs/submit/hadoop">hadoop command</a>.</p><p>--cluster ← This gcloud flag specifies to which dataproc cluster you’re submitting your job.</p><p>--class ← This gcloud flag specifies the main class (org.apache.sqoop.Sqoop) that you want the Dataproc job to run. Behind the scenes, Dataproc is calling the Hadoop tool and running this main class much the same as the <a href="https://github.com/apache/sqoop/blob/11c83f68386add243762929ecf7f6f25a99efbf4/bin/sqoop#L101">Sqoop command line tool does</a>. <strong>Note: Make sure you include the path to the jar file of the main class in the </strong>--jars<strong> flag (e.g. </strong>--jars=gs://&lt;GCS_BUCKET&gt;/sqoop-1.4.7-hadoop260.jar<strong>).</strong></p><p>--jars ←This gcloud flag specifies a comma separated list of jar file <strong>paths </strong>(paths should point to files in GCS or in the Dataproc cluster). These jar files are provided to the MapReduce and driver classpaths in the Dataproc cluster. To run Sqoop in Dataproc, you need to provide the following jar files:</p><ul><li>Latest Sqoop release (<a href="http://central.maven.org/maven2/org/apache/sqoop/sqoop/1.4.7/sqoop-1.4.7-hadoop260.jar">sqoop-1.4.7-hadoop260.jar</a>) (<a href="https://mvnrepository.com/artifact/org.apache.sqoop/sqoop">Maven Site</a> to check for latest)</li><li>JDBC drivers needed to connect to your database. For the MySQL driver, simply add the file path to the MySQL driver that exists in the Dataproc cluster (file:///usr/share/java/mysql-connector-java-5.1.42.jar). If you’re connecting to another database, DB2 for example, include the DB2 JDBC drivers necessary for that database.</li><li>Any jar files necessary for serializing data in your specified output format. In this example, you store the imported MySQL data as Avro files, so you include the Avro jar (<a href="https://repo1.maven.org/maven2/org/apache/avro/avro-tools/1.8.2/avro-tools-1.8.2.jar">avro-tools-1.8.2.jar</a>) (<a href="https://mvnrepository.com/artifact/org.apache.avro/avro-tools">Maven Site</a> to check for latest). <strong>Note: The Sqoop tool itself requires minimum Avro version 1.8.0 to run properly, so the Avro jar is necessary even if you don’t plan on storing data in Avro format.</strong></li><li>(Optional) Hive driver that already exists in Dataproc (file:///usr/lib/hive/lib/hive-exec.jar). <strong>Note: This Hive jar is only necessary when you use Sqoop to import data directly into Dataproc’s Hive warehouse.</strong></li></ul><p>-- import ←The single -- gcloud argument separates the gcloud specific flags on the left and the hadoop job args on the right. Since you passed the Sqoop main class to the hadoop tool (via --class flag), you must then also specify the Sqoop tool (import in this example) that you want the main class to run. After import, you specify all the arguments to the <a href="https://sqoop.apache.org/docs/1.4.7/SqoopUserGuide.html#_literal_sqoop_import_literal">Sqoop import tool</a>.</p><p>-Dmapreduce.job.user.classpath.first=true ←This Hadoop argument is necessary to instruct Hadoop to give preference to the user provided jars (passed via the --jars flag) over the default jars included in the cluster. Sqoop requires version 1.8.0 of Avro and the native Dataproc version of Avro, as of this writing, is 1.7.7. Setting this argument to true will give Sqoop the correct version of Avro jar which you pass in the--jars list.</p><p>-—connect= ←This Sqoop argument specifies the JDBC connection string: <em>jdbc:mysql://&lt;HOSTNAME&gt;:&lt;PORT&gt;/&lt;DATABASE_NAME&gt; . </em><strong>Note: If you’re connecting to your database using Cloud SQL Proxy, make sure that HOSTNAME is set to ‘localhost’ and that the port you use is the same one the proxy used when you created the Dataproc cluster </strong>(e.g. make sure the cluster creation argument--metadata=additional-cloud-sql-instances=&lt;PROJECT_ID&gt;:&lt;REGION&gt;:&lt;<a href="https://cloud.google.com/sql/docs/mysql/instance-info">SQL_INSTANCE_NAME</a>&gt;=tcp:&lt;<strong>PORT</strong>&gt; and your Sqoop connect argument --connect=jdbc:mysql://&lt;HOSTNAME&gt;:&lt;<strong>PORT</strong>&gt;/&lt;DATABASE_NAME&gt; share the same port).</p><p>--username= ←This Sqoop argument specifies the username to use when authenticating to the relational database.</p><p>--password-file= ←This Sqoop argument specifies the path to a file in GCS (Google Cloud Storage) which contains the password to your database. <strong>Warning: Do not use the </strong>--password<strong> option instead of </strong>--password-file<strong> as this will leave your password exposed in plain sight in logs.</strong></p><p>--target-dir= ←Traditionally when running Sqoop in Hadoop, you’d set this Sqoop argument to an HDFS (hdfs://) directory path, but since you’re using Dataproc you can take advantage of the built in GCS connector and set this to a GCS (gs://) directory path. This means Sqoop will store the imported data in the GCS location you specify, skipping HDFS altogether.</p><p>--table= ←This Sqoop argument specifies the database table you want to import.</p><p>--as-avrodatafile ←Specify this Sqoop flag to store all imported data in Avro file format. Avro format has the advantage of both compressing data in binary format and storing the table schema in the same file.</p><p>--hive-import ← Specify this Sqoop flag to store all imported data into a Hive table. <strong>Note: When you use the </strong>--hive-import<strong> flag, make sure your Dataproc cluster was created using the </strong>--properties=hive:hive.metastore.warehouse.dir=gs://&lt;GCS_BUCKET&gt;/hive-warehouse<strong> flag so you persist your Hive data in GCS.</strong></p><h3>4 Steps for Automated Dataproc Job Submission using Workflow Templates</h3><p><strong>1. Create workflow</strong></p><ul><li><em>Creating a workflow template does not itself create a Cloud Dataproc cluster or create and submit jobs. The template is simply the set of instructions. Clusters and jobs are only created and executed when a workflow template is </em><strong><em>instantiated </em></strong><em>(See Step 4).</em></li></ul><pre>gcloud beta dataproc workflow-templates create &lt;<strong>TEMPLATE_ID</strong>&gt;</pre><p><strong>2. Add workflow cluster creation properties</strong></p><ul><li><em>Many cluster properties have been purposely left out for simplicity</em></li></ul><pre>gcloud beta dataproc workflow-templates set-managed-cluster &lt;<strong>TEMPLATE_ID</strong>&gt; --zone=&lt;<strong>ZONE</strong>&gt; --cluster-name=&lt;<strong>CLUSTER_NAME</strong>&gt;</pre><p><strong>3. Add a job(s) to your workflow to run on the cluster</strong></p><ul><li><em>Add the job below to your workflow if you want Sqoop to import your database table as Avro files into Cloud Storage, which you’ll then </em><a href="https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-avro#bigquery-import-gcs-file-cli"><em>load into BigQuery</em></a><em> using the bq tool.</em></li></ul><pre>gcloud beta dataproc workflow-templates add-job hadoop --step-id=&lt;<strong>STEP_ID</strong>&gt; --workflow-template=&lt;<strong>TEMPLATE_ID</strong>&gt; --class=org.apache.sqoop.Sqoop --jars=gs://&lt;<strong>GCS_BUCKET</strong>&gt;/sqoop-1.4.7-hadoop260.jar,gs://&lt;<strong>GCS_BUCKET</strong>&gt;/avro-tools-1.8.2.jar,file:///usr/share/java/mysql-connector-java-5.1.42.jar -- import -Dmapreduce.job.user.classpath.first=true --connect=jdbc:mysql://&lt;<strong>HOSTNAME</strong>&gt;:&lt;<strong>PORT</strong>&gt;/&lt;<strong>DATABASE_NAME</strong>&gt; --username=sqoop --password-file=gs://&lt;<strong>GCS_BUCKET</strong>&gt;/passwordFile.txt --target-dir=gs://&lt;<strong>GCS_BUCKET</strong>&gt;/mysql_output --table=&lt;<strong>TABLE</strong>&gt; --as-avrodatafile</pre><ul><li><em>Add the job below to your workflow if you want Sqoop to import your database table into a Hive table. </em><strong><em>Note: The command below adds one more jar </em></strong>file:///usr/lib/hive/lib/hive-exec.jar<strong><em> to the </em></strong>— -jars=<strong><em> argument and replaces </em></strong>--as-avrodatafile<strong><em> with </em></strong>--hive-import</li></ul><pre>gcloud beta dataproc workflow-templates add-job hadoop --step-id=&lt;<strong>STEP_ID</strong>&gt; --workflow-template=&lt;<strong>TEMPLATE_ID</strong>&gt; --class=org.apache.sqoop.Sqoop --jars=gs://&lt;<strong>GCS_BUCKET</strong>&gt;/sqoop-1.4.7-hadoop260.jar,gs://&lt;<strong>GCS_BUCKET</strong>&gt;/avro-tools-1.8.2.jar,file:///usr/share/java/mysql-connector-java-5.1.42.jar,file:///usr/lib/hive/lib/hive-exec.jar -- import -Dmapreduce.job.user.classpath.first=true --connect=jdbc:mysql://&lt;<strong>HOSTNAME</strong>&gt;:&lt;<strong>PORT</strong>&gt;/&lt;<strong>DATABASE_NAME</strong>&gt; --username=&lt;<strong>DATABASE_USERNAME</strong>&gt; --password-file=gs://&lt;<strong>GCS_BUCKET</strong>&gt;/passwordFile.txt --table=&lt;<strong>TABLE</strong>&gt; --hive-import</pre><p><strong>4. Instantiate/Run workflow</strong></p><ul><li><em>Multiple (simultaneous) instantiations of a template are supported.</em></li></ul><pre>gcloud beta dataproc workflow-templates instantiate &lt;<strong>TEMPLATE_ID</strong>&gt;</pre><h3>Loading Your Sqoop-Import Data into BigQuery</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/125/1*g28KzVazHM0vNf5Et0S-dg.png" /></figure><p>If you use Sqoop to import your database table into Cloud Storage, you can simply <a href="https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-avro#bigquery-import-gcs-file-cli">load it into BigQuery</a> using the <a href="https://cloud.google.com/bigquery/docs/bq-command-line-tool">bq command-line tool</a>:</p><pre>bq load --source_format=AVRO &lt;<strong>YOUR_DATASET</strong>&gt;.&lt;<strong>YOUR_TABLE</strong>&gt; gs://&lt;<strong>GCS_BUCKET</strong>&gt;/mysql_output/*.avro</pre><h3>Querying Your Hive Tables with Dataproc Hive Jobs</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/125/1*55-nhaKWnnOgvPdS36w5Kg.png" /></figure><p>If you use Sqoop to import your database table into Hive in Dataproc, you can run SQL <a href="https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-SQLOperations">queries</a> on your Hive warehouse by submitting a <a href="https://cloud.google.com/sdk/gcloud/reference/dataproc/jobs/submit/hive">Hive job</a> to a Dataproc cluster:</p><pre>gcloud dataproc jobs submit hive --cluster=&lt;<strong>CLUSTER_NAME</strong>&gt; -e=&quot;SELECT * FROM &lt;<strong>TABLE</strong>&gt;&quot;</pre><p><strong>Note: Make sure you run these Hive jobs on a Dataproc cluster that has the default Hive warehouse directory pointing to the same GCS bucket that contains your Sqoop-exported data (e.g. your cluster should be created with</strong>--properties=hive:hive.metastore.warehouse.dir=gs://&lt;<strong>GCS_BUCKET</strong>&gt;/hive-warehouse<strong>).</strong></p><h4>Hope this guide serves you well with many scoops of delicious data!</h4><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4056b8fa2600" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/moving-data-with-apache-sqoop-in-google-cloud-dataproc-4056b8fa2600">Moving Data with Apache Sqoop in Google Cloud Dataproc</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>