<?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 Alex Fragotsis on Medium]]></title>
        <description><![CDATA[Stories by Alex Fragotsis on Medium]]></description>
        <link>https://medium.com/@alexfragotsis?source=rss-18b8f379ecf2------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*hnV-SrEe5nWHsSXs.</url>
            <title>Stories by Alex Fragotsis on Medium</title>
            <link>https://medium.com/@alexfragotsis?source=rss-18b8f379ecf2------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 20:34:42 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@alexfragotsis/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[Streaming Data to BigQuery with Dataflow and Updating the Schema in Real-Time]]></title>
            <link>https://medium.com/inside-league/streaming-data-to-bigquery-with-dataflow-and-real-time-schema-updating-c7a3deba3bad?source=rss-18b8f379ecf2------2</link>
            <guid isPermaLink="false">https://medium.com/p/c7a3deba3bad</guid>
            <category><![CDATA[streaming]]></category>
            <category><![CDATA[dataflow]]></category>
            <category><![CDATA[apache-beam]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[schema]]></category>
            <dc:creator><![CDATA[Alex Fragotsis]]></dc:creator>
            <pubDate>Sun, 26 Dec 2021 17:44:24 GMT</pubDate>
            <atom:updated>2021-12-26T17:46:31.487Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_8PWNQ8X-Gq8MTaVI52PKA.jpeg" /><figcaption><em>Robert Delaunay, “</em><a href="https://www.alamy.com/stock-photo-robert-delaunay-relief-disques-1936-134895415.html"><em>Relief-disques</em></a><em>,” 1936.</em></figcaption></figure><p>In our previous <a href="https://medium.com/inside-league/loading-complex-json-files-in-realtime-to-bigquery-from-pubsub-using-dataflow-and-updating-the-49b420838bac">story</a>, we saw how to stream data to Big Query and also add new columns when needed. This solution though is not really real-time, I think we can do better.</p><p>Another approach I’ve seen discussed online, but haven’t found any code samples, is this. We enable streaming inserts to Big Query using Dataflow, if the new data contain new fields, the insert is going to fail, then we get all the failed rows, detect the schema, update the schema in BQ and then re-insert.</p><p>A really simple pipeline that streams data to Big Query looks like this:</p><pre>def run(argv):<br>  with beam.Pipeline(options=pipeline_options) as pipeline:<br>    realtime_data = (<br>      pipeline<br>       | &quot;Read PubSub Messages&quot; &gt;&gt;<br>          beam.io.ReadFromPubSub(<br>           subscription=options.input_subscription,                                                                  <br>           with_attributes=True)<br>       | f&quot;Write to {options.bq_table}&quot; &gt;&gt; <br>          beam.io.WriteToBigQuery(<br>            table=f&quot;{options.bq_dataset}.{options.bq_table}&quot;,<br>        write_disposition=beam.io.BigQueryDisposition.WRITE_APPEND) <br>   )</pre><p>Now, if the PubSub message contains some new fields that are missing from Big Query, the beam.io.WriteToBigQuery , according to the <a href="https://beam.apache.org/releases/pydoc/2.15.0/_modules/apache_beam/io/gcp/bigquery.html">documentation</a>, is going to emit the failed rows to</p><p>beam.io.gcp.bigquery.BigQueryWriteFn.FAILED_ROWS</p><p>So all we have to do is just read them. Group them in a small window (I use 1 minute), just to catch any other messages that happen to come at the same time, and re-insert them to Big Query</p><pre>(<br>  realtime_data[beam.io.gcp.bigquery.BigQueryWriteFn.FAILED_ROWS]<br>   | f&quot;Window&quot; &gt;&gt;  <br>     GroupWindowsIntoBatches(window_size=options.bq_window_size)<br>   | f&quot;Failed Rows for {table}&quot; &gt;&gt;<br>     beam.ParDo(ModifyBadRows(options.bq_dataset, options.bq_table))<br>)</pre><p><strong>BUT!</strong></p><p>before we start testing there are a few gotchas.<br>1. we need to change beam.io.WriteToBigQuery retry policy to Never.</p><pre>beam.io.WriteToBigQuery(<br>    table=f&quot;{options.bq_dataset}.{options.bq_table}&quot;,<br>    write_disposition=beam.io.BigQueryDisposition.WRITE_APPEND,   <br><strong>insert_retry_strategy=beam.io.gcp.bigquery_tools.RetryStrategy.RETRY_NEVER</strong><br>)</pre><p>2. The default GroupWindowsIntoBatches we find in Google’s <a href="https://cloud.google.com/pubsub/docs/pubsub-dataflow">documentation</a> doesn’t work. Messages coming from BigQueryWriteFn.FAILED_ROWS are not timestamped, so we need to timestamp them</p><pre>import time</pre><pre>class GroupWindowsIntoBatches(beam.PTransform):</pre><pre>def __init__(self, window_size):<br>    # Convert minutes into seconds.<br>    self.window_size = int(window_size * 60)</pre><pre>def expand(self, pcoll):<br>  return (pcoll<br>    <strong>| &#39;Add Timestamps&#39; &gt;&gt; </strong><br>    <strong>beam.Map(lambda x: beam.window.TimestampedValue(x, time.time()))</strong><br>    | &quot;Window into Fixed Intervals&quot; &gt;&gt;<br>   beam.WindowInto(window.FixedWindows(self.window_size))<br>    | &quot;Groupby&quot; &gt;&gt; beam.GroupByKey()<br>    | &quot;Abandon Dummy Key&quot; &gt;&gt; beam.MapTuple(lambda _, val: val)<br>  )<br></pre><p>Finally, to detect new schema we use the <a href="https://pypi.org/project/bigquery-schema-generator/">BigQuery Schema Generator</a>, as the last time</p><pre>class ModifyBadRows(beam.DoFn):<br><br>  def __init__(self, bq_dataset, bq_table):<br>   self.bq_dataset = bq_dataset<br>    self.bq_table = bq_table<br><br>  def start_bundle(self):<br>    self.client = bigquery.Client()<br><br>  def process(self, batch):<br>    logging.info(f&quot;Got {len(batch)} bad rows&quot;)<br>    table_id = f&quot;{self.bq_dataset}.{self.bq_table}&quot;<br><br>    generator = SchemaGenerator(input_format=&#39;dict&#39;, <br>                                    quoted_values_are_strings=True)<br><br>    # Get original schema to assist the deduce_schema function.<br>    # If the table doesn&#39;t exist<br>    # proceed with empty original_schema_map<br>    try:<br>      table_file_name =f&quot;original_schema_{self.bq_table}.json&quot;<br>      table = self.client.get_table(table_id)<br>      self.client.schema_to_json(table.schema, table_file_name)<br>      original_schema_map =<br>                    read_existing_schema_from_file(table_file_name)<br>    except Exception:<br>      logging.info(f&quot;{table_id} table not exists. Proceed without getting schema&quot;)<br>      original_schema_map = {}<br><br>    # generate the new schema<br>    schema_map, error_logs = generator.deduce_schema(<br>                                                   input_data=batch,<br>                                     schema_map=original_schema_map)<br>    schema = generator.flatten_schema(schema_map)<br><br>    job_config = bigquery.LoadJobConfig(<br>                 source_format=<br>                       bigquery.SourceFormat.NEWLINE_DELIMITED_JSON,<br>                 schema_update_options=[<br>                    bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION<br>                 ],<br>                 write_disposition=<br>                             bigquery.WriteDisposition.WRITE_APPEND,<br>                 schema=schema<br>    )<br><br>    try:<br>      load_job = self.client.load_table_from_json(<br>      batch,<br>      table_id,<br>      job_config=job_config,<br>      )  # Make an API request.<br><br>      load_job.result()  # Waits for the job to complete.<br>      if load_job.errors:<br>        logging.info(f&quot;error_result =  {load_job.error_result}&quot;)<br>        logging.info(f&quot;errors =  {load_job.errors}&quot;)<br><em>      </em>else:<br>        logging.info(f&#39;Loaded {len(batch)} rows.&#39;)<br><br>    except Exception as error:<br>      logging.info(f&#39;Error: {error} with loading dataframe&#39;)</pre><p>And that’s it! Now our pipeline will stream the data to Big Query in <strong>real-time</strong>,<strong> </strong>and<strong> </strong>if we get a message that contains a field that we don’t have a column in Big Query:</p><ul><li>that insertion will fail,</li><li>we’ll gather all failed rows and group them in a 1-minute window</li><li>our pipeline will automatically detect the new schema</li><li>update Big Query and</li><li>re-insert the failed rows</li></ul><p>and all that <strong>without stopping the pipeline at all, </strong>messages that will arrive after the failed one will get inserted to Big Query.</p><p>You can find the full code here:<br><a href="https://github.com/aFrag/PubsubToBigQuery/blob/main/StreamingPubsubToBQ.py">https://github.com/aFrag/PubsubToBigQuery/blob/main/StreamingPubsubToBQ.py</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c7a3deba3bad" width="1" height="1" alt=""><hr><p><a href="https://medium.com/inside-league/streaming-data-to-bigquery-with-dataflow-and-real-time-schema-updating-c7a3deba3bad">Streaming Data to BigQuery with Dataflow and Updating the Schema in Real-Time</a> was originally published in <a href="https://medium.com/inside-league">Inside League</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Authenticated calls to cloud functions with Python]]></title>
            <link>https://alexfragotsis.medium.com/authenticated-calls-to-cloud-functions-with-python-d23aadb24ebd?source=rss-18b8f379ecf2------2</link>
            <guid isPermaLink="false">https://medium.com/p/d23aadb24ebd</guid>
            <category><![CDATA[authentication]]></category>
            <category><![CDATA[google-cloud-functions]]></category>
            <category><![CDATA[cloud-functions]]></category>
            <category><![CDATA[dataflow]]></category>
            <category><![CDATA[pub-sub]]></category>
            <dc:creator><![CDATA[Alex Fragotsis]]></dc:creator>
            <pubDate>Fri, 04 Jun 2021 17:35:40 GMT</pubDate>
            <atom:updated>2021-06-04T17:35:40.913Z</atom:updated>
            <content:encoded><![CDATA[<h4>The past few weeks I developed and deployed a cloud function that is supposed to get called only by authorized users/service accounts and the truth is that the documentation I found wasn’t really helpful.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*fYyqwyg2Gc9D2mEC.png" /></figure><p>First I created a service account, gave it roles/cloudfunctions.invoker permission. If you’re dealing with an extracted service account the code is pretty simple</p><pre>from google.oauth2 import service_account <br>from google.auth.transport.requests import AuthorizedSession</pre><pre>keypath = &#39;.../my_sa_key.json</pre><pre>&#39;base_url=&#39;https://us-central1-my_project.cloudfunctions.net/my_function&#39;</pre><pre>creds=service_account.IDTokenCredentials.from_service_account_file(      keypath, target_audience=base_url)</pre><pre>authed_session = AuthorizedSession(creds)</pre><pre># make authenticated request and print the response, status_code<br>resp = authed_session.get(base_url)<br>print(resp.status_code)<br>print(resp.text)</pre><h4>Attempt 1</h4><p>But in my case, the call to the cloud function is going to happen from within a google service (Dataflow) and I don’t have access to the service account file. So I tried to find how can I use target_audience with the default credentials. According to the <a href="https://cloud.google.com/functions/docs/securing/authenticating">documentation</a> we need to generate a token and use it in the headers</p><pre>import urllib</pre><pre>import google.auth.transport.requests<br>import google.oauth2.id_token<br></pre><pre>def make_authorized_get_request(service_url):<br>    &quot;&quot;&quot;<br>    make_authorized_get_request makes a GET request to the specified HTTP endpoint<br>    in service_url (must be a complete URL) by authenticating with the<br>    ID token obtained from the google-auth client library.<br>    &quot;&quot;&quot;</pre><pre>    req = urllib.request.Request(service_url)</pre><pre>    auth_req = google.auth.transport.requests.Request()<br>    id_token = google.oauth2.id_token.fetch_id_token(auth_req, service_url)</pre><pre>    req.add_header(&quot;Authorization&quot;, f&quot;Bearer {id_token}&quot;)<br>    response = urllib.request.urlopen(req)</pre><pre>    return response.read()</pre><p>Even though that seemed like a good idea since <a href="http://google.oauth2.id_token.fetch_id_token/">fetch_id_token</a> will try to find the token from various sources. In my case, it didn’t work. Calling that from Dataflow gives megoogle.oauth2&#39; has no attribute ‘id_token’</p><h4>Attempt 2</h4><p>So I searched more and another solution came from a post on <a href="https://stackoverflow.com/questions/57915672/why-is-authentication-to-cloud-functions-different-than-cloud-endpoints">StackOverflow</a>.</p><pre>from google.oauth2 import service_account<br>from google.auth.transport.requests import AuthorizedSession</pre><pre>base_url=&#39;https://us-central1-my_project.cloudfunctions.net/my_function</pre><pre>&#39;IAM_SCOPE = &#39;<a href="https://www.googleapis.com/auth/iam&#39;">https://www.googleapis.com/auth/iam&#39;</a><br>OAUTH_TOKEN_URI = &#39;<a href="https://www.googleapis.com/oauth2/v4/token&#39;">https://www.googleapis.com/oauth2/v4/token&#39;</a></pre><pre>my_credentials, _ = google.auth.default(scopes=[IAM_SCOPE])</pre><pre>signer_email = my_credentials.service_account_email<br>signer = my_credentials.signercreds = google.oauth2.service_account.IDTokenCredentials(signer, signer_email, token_uri=OAUTH_TOKEN_URI, target_audience=base_url)</pre><pre>authed_session = AuthorizedSession(creds)</pre><pre>resp = authed_session.get(base_url)<br>print(resp.status_code)<br>print(resp.text)</pre><p>That, too, worked when I ran it on my local dev, but deploying it to Dataflow I was getting more errors. Even though the my_credentials had the correct signer, email etc.. I was getting</p><pre>&quot;Error calling the IAM signBytes API: {}&quot;.format(response.data) google.auth.exceptions.TransportError: Error calling the IAM signBytes API: <br>&#39;{ <br>  &quot;error&quot;: { <br>    &quot;code&quot;: 403, <br>    &quot;message&quot;: &quot;The caller does not have permission&quot;,<br>    &quot;status&quot;: &quot;PERMISSION_DENIED&quot; <br>  }<br>}&#39;</pre><p>Back to Google then…</p><h4>Attempt 3 (Solution)</h4><p>I accidentally found <a href="https://cloud.google.com/run/docs/authenticating/service-to-service">this</a> article explaining how to authenticate from service to service on GCP. And even though I don’t like the idea, it worked!</p><p>Created a function to get the JWT token, I also used <a href="https://pypi.org/project/cachetools/">cache-tools</a> to cache the token.</p><pre>@cached(cache=TTLCache(maxsize=1024, ttl=3600 - 30))<br>def get_token(base_url):<br>    logging.info(&quot;Getting token id&quot;)<br>    # Set up metadata server request<br>    metadata_server_token_url = &#39;http://metadata/computeMetadata/v1/instance/service-accounts/default/identity?audience=&#39;<br><br>    token_request_url = metadata_server_token_url + base_url<br>    token_request_headers = {&#39;Metadata-Flavor&#39;: &#39;Google&#39;}<br><br>    # Fetch the token<br>    token_response = requests.get(token_request_url, headers=token_request_headers)<br>    jwt = token_response.content.decode(&quot;utf-8&quot;)<br>    return jwt</pre><p>and use the token in the headers</p><pre>headers = {<br>    &#39;Authorization&#39;: f&#39;Bearer {get_token(self.base_url)}&#39;,<br>    &#39;Content-Type&#39;: &#39;application/fhir+json;charset=utf-8&#39;<br>}<br><br>service_response = requests.put(base_url, headers=headers, json=data)<br>print(f&quot;service_response_code = {service_response.status_code}&quot;)</pre><p>Full Dataflow Code</p><pre>@cached(cache=TTLCache(maxsize=1024, ttl=3600 - 30))<br>def get_token(base_url):<br>    metadata_server_token_url = &#39;http://metadata/computeMetadata/v1/instance/service-accounts/default/identity?audience=&#39;<br><br>    token_request_url = metadata_server_token_url + base_url<br>    token_request_headers = {&#39;Metadata-Flavor&#39;: &#39;Google&#39;}<br><br>    # Fetch the token<br>    token_response = requests.get(token_request_url, headers=token_request_headers)<br>    jwt = token_response.content.decode(&quot;utf-8&quot;)<br>    return jwt<br><br><br>class WriteToCloudFunction(beam.DoFn):<br><br>    def __init__(self):<br>        self.base_url = &quot;https://us-central1-my_project.cloudfunctions.net/my_function&quot;<br><br>    def process(self, message):<br><br>        data = json.loads(message.data.decode(&quot;utf-8&quot;))<br><br>        headers = {<br>            &#39;Authorization&#39;: f&#39;Bearer {get_token(self.base_url)}&#39;,<br>            &#39;Content-Type&#39;: &#39;application/fhir+json;charset=utf-8&#39;<br>        }<br><br>        service_response = requests.put(self.base_url, headers=headers, json=data)<br>        logging.info(f&quot;service_response_code = {service_response.status_code}&quot;)</pre><pre>with beam.Pipeline(options=pipeline_options) as pipeline:<br>  (<br>    pipeline<br>    | &quot;Read PubSub Messages&quot; &gt;&gt; beam.io.ReadFromPubSub(subscription=options.input_subscription,<br>with_attributes=True)<br>    | .....<br>    | &quot;Write to CF&quot; &gt;&gt; beam.ParDo(WriteToCloudFunction())</pre><ul><li>** For the caching to work the get_token function needs to be outside the beam.DoFn class</li></ul><p>In times like this, I’m super thankful for <a href="https://medium.com/u/d53dd768d047?source=post_page-----63ffe7006ce8--------------------------------">Stack Overflow</a> and all the people who contribute there!</p><p>I hope this article will help people like me who are frustrated with similar issues.</p><p>Let me know if you have another/a better way to make authenticated calls on cloud functions and I can add it here!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d23aadb24ebd" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Loading complex JSON files in RealTime to BigQuery from PubSub using Dataflow and updating the…]]></title>
            <link>https://medium.com/inside-league/loading-complex-json-files-in-realtime-to-bigquery-from-pubsub-using-dataflow-and-updating-the-49b420838bac?source=rss-18b8f379ecf2------2</link>
            <guid isPermaLink="false">https://medium.com/p/49b420838bac</guid>
            <category><![CDATA[pub-sub]]></category>
            <category><![CDATA[json]]></category>
            <category><![CDATA[dataflow]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[engineering]]></category>
            <dc:creator><![CDATA[Alex Fragotsis]]></dc:creator>
            <pubDate>Sun, 17 Jan 2021 18:08:06 GMT</pubDate>
            <atom:updated>2021-05-18T14:17:56.464Z</atom:updated>
            <content:encoded><![CDATA[<h3>Loading complex JSON files in RealTime to BigQuery from PubSub using Dataflow and updating the schema</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gpX_-ipZXabc81gF0HCXaw.png" /></figure><p>In my previous post, I explained <a href="https://medium.com/inside-league/real-time-streaming-salesforce-updates-to-pubsub-d9aedd5973ca">how to stream data from Salesforce to PubSub in real-time</a>. The next logical step would be to store the data somewhere, right?</p><p>One option could be, for example, to batch the data, and write them to files in GCS. That’s a good start and guarantee that we won’t lose anything but we won’t have an easy way to query the data. So In addition to GCS, we can insert the data to BigQuery.</p><p>So when we talk about PubSub and real-time pipelines Dataflow is our friend. We can build a simple pipeline to read data from PubSub and write them to Big Query and to GCS simultaneously. Something like that</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/307/1*ROWeYqxyfKRWTo3gufAtVQ.png" /></figure><p>So let’s dive into the code.</p><h4>The pipeline</h4><pre>def run(argv):<br>    parser = argparse.ArgumentParser()<br>    known_args, pipeline_args = parser.parse_known_args(argv)<br>    pipeline_options = PipelineOptions(pipeline_args, save_main_session=True, streaming=True)<br>    options = pipeline_options.view_as(JobOptions)<br><br>    with beam.Pipeline(options=pipeline_options) as pipeline:<br>        (<br>            pipeline<br>            | &quot;Read PubSub Messages&quot; &gt;&gt; beam.io.ReadFromPubSub(subscription=options.input_subscription)<br>            | &quot;Add timestamps to messages&quot; &gt;&gt; beam.ParDo(AddTimestamps())<br>            | f&quot;Window into: {options.window_size}m&quot; &gt;&gt; GroupWindowsIntoBatches(options.window_size)<br>            | &quot;Export Raw Data to GCS&quot; &gt;&gt; beam.ParDo(WriteToGCS(output_path=options.output_path, output_file_prefix=options.output_file_prefix))<br>            | &quot;Write Raw Data to Big Query&quot; &gt;&gt; beam.ParDo(WriteDataframeToBQ(project_id=options.project_id, bq_dataset=options.bigquery_dataset, bq_table=options.bigquery_table))<br>        )</pre><p>and the step that writes to Big Query</p><pre>class WriteDataframeToBQ(beam.DoFn):<br><br>    def __init__(self, bq_dataset, bq_table, project_id):<br>        self.bq_dataset = bq_dataset<br>        self.bq_table = bq_table<br>        self.project_id = project_id<br><br>    def start_bundle(self):<br>        self.client = bigquery.Client()<br><br>    def process(self, df):<br>        # table where we&#39;re going to store the data<br>        table_id = f&quot;{self.bq_dataset}.{self.bq_table}&quot;<br><br>        job_config = bigquery.LoadJobConfig(<br>            <strong>autodetect=True,</strong><br>          source_format=bigquery.SourceFormat.NEWLINE_DELIMITED_JSON,<br>            schema_update_options=[<br>                bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION,<br>                bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION<br>            ],<br>            write_disposition=bigquery.WriteDisposition.WRITE_APPEND<br>        )<br><br>        try:<br>            load_job = self.client.load_table_from_dataframe(<br>                df,<br>                table_id,<br>                job_config=job_config,<br>            )  # Make an API request.<br><br>            load_job.result()  # Waits for the job to complete.<br>            if load_job.errors:<br>                logging.info(f&quot;result={load_job.error_result}&quot;)<br>                logging.info(f&quot;errors = {load_job.errors}&quot;)<br>            else:<br>                logging.info(f&#39;Loaded {len(df)} rows.&#39;)<br><br>        except Exception as error:<br>            logging.info(f&#39;Error: {error} with loading dataframe&#39;)<br><br>            if load_job.errors:<br>                logging.info(f&quot;result={load_job.error_result}&quot;)<br>                logging.info(f&quot;errors={load_job.errors}&quot;)</pre><p>Now, the data that we get from salesforce are in a pretty straight forward schema. We don’t have any nested fields, everything is on one level and the data we have are pretty simple.</p><pre>{<br>  &quot;id&quot;: &quot;2003c000005c4dFEQD&quot;,<br>  &quot;isdeleted&quot;: false,<br>  &quot;casenumber&quot;: &quot;01286593&quot;,<br>  &quot;contactid&quot;: &quot;0012c000004IURN4T4&quot;,<br>  &quot;accountid&quot;: null,<br>  &quot;assetid&quot;: null,<br>  &quot;businesshoursid&quot;: &quot;05m40000000UuckwABD&quot;,<br>  &quot;parentid&quot;: null,<br>  &quot;suppliedname&quot;: Alex,<br>  &quot;suppliedemail&quot;: alextest@test.ca,<br>  &quot;suppliedphone&quot;: &quot;1234567890&quot;,<br>  &quot;suppliedcompany&quot;: &quot;my Super Awesome Company&quot;,<br>  &quot;type&quot;: null,<br>  &quot;recordtypeid&quot;: &quot;8594t0000006ITcA5T&quot;,<br>  &quot;status&quot;: &quot;New&quot;,<br>  &quot;reason&quot;: null,<br>  &quot;origin&quot;: &quot;Offline Chat Request&quot;,<br>  &quot;subject&quot;: &quot;Offline Chat Request from: Alex Test&quot;,<br>  &quot;priority&quot;: &quot;Medium&quot;,<br>  &quot;description&quot;: &quot;testing offline flow&quot;,<br>  &quot;isclosed&quot;: false,<br>  &quot;closeddate&quot;: null,<br>  &quot;isescalated&quot;: false,<br>  &quot;ownerid&quot;: &quot;4512c000000ORHTDA4&quot;,<br>  &quot;isclosedoncreate&quot;: false,<br>  &quot;createddate&quot;: &quot;2020-10-23T21:13:51.000+0000&quot;,<br>  &quot;createdbyid&quot;: &quot;4512c000000ORHTDA4&quot;,<br>  &quot;lastmodifieddate&quot;: &quot;2020-10-23T21:13:54.000+0000&quot;,<br>  &quot;lastmodifiedbyid&quot;: &quot;4512c000000ORHTDA4&quot;,<br>  &quot;systemmodstamp&quot;: &quot;2020-10-23T21:13:54.000+0000&quot;,<br>  &quot;lastvieweddate&quot;: null,<br>  &quot;lastreferenceddate&quot;: null,<br>}</pre><p>But if we try to use the same code for some more complicated data. Data that contain nested arrays, with objects and repeated fields, unfortunately, this isn’t going to work</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-vMXmdKYgVuTaDG_zDPxcQ.png" /><figcaption>The error I was getting when I tried to upload a more complicated data frame.</figcaption></figure><h4>Generate BQ Schema</h4><p>So as a “proper engineer”, I don’t want to sit down and write the whole schema by myself. I would prefer to find an automated way, which would also handle the case of new (nested) field additions without worrying that they’re going to break my pipeline.</p><p>So here’s where <a href="https://pypi.org/project/bigquery-schema-generator/">BigQuery Schema Generator</a> comes into play. This package works really well and given a JSON object it can generate the BigQuery schema which we can use in thejob_config to fix the above error. Here’s how we can do it</p><pre>from bigquery_schema_generator.generate_schema import SchemaGenerator<br>from google.cloud import bigquery</pre><pre><strong>generator = SchemaGenerator(input_format=&#39;dict&#39;, <br>                            quoted_values_are_strings=True, <br>                            keep_nulls=True)</strong><br># convert dataframe to dict<br>df_dict = df.to_dict(&#39;records&#39;)</pre><pre># generate the new schema<br><strong>schema_map,errors = generator.deduce_schema(input_data=df_dict)<br>bq_schema = generator.flatten_schema(schema_map)</strong></pre><p>Now we can use bq_schema into thejob_config like this</p><pre>job_config = bigquery.LoadJobConfig(<br>    source_format=bigquery.SourceFormat.NEWLINE_DELIMITED_JSON,<br>    schema_update_options=[<br>        bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION,<br>        bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION<br>    ],<br>    write_disposition=bigquery.WriteDisposition.WRITE_APPEND,<br>    <strong>schema = bq_schema</strong><br>)</pre><h4>Make it Better</h4><p>This should work fine and we would be able to add new fields to our BigQuery table without any issues, but why don’t we go one step further?<br>We can help deduce_schema method to determine the schema by passing the original schema from the table. <br>So before every insert, we can get the schema from the table and use it as an input to help generate the bq schema. In that way, the schema generation process will start from the original schema, not from 0.</p><p>This will add some complexity to our code but I think eventually the generated schema would be more accurate, especially if we have missing and null fields in the messages we get from PubSub.</p><p>So first of all, we need to get the schema from the Big Query table</p><pre>from google.cloud import bigquery</pre><pre>client = bigquery.Client()</pre><pre>generator = SchemaGenerator(input_format=&#39;dict&#39;, <br>                            quoted_values_are_strings=True, <br>                            keep_nulls=True)</pre><pre>try:<br>    table_file_name = f&quot;original_schema_{table_id}.json&quot;<br>    table = client.get_table(table_id)<br>    client.schema_to_json(<strong>table.schema</strong>, table_file_name)<br>    <strong>original_schema_map</strong> = <br>                     read_existing_schema_from_file(table_file_name)<br>except Exception:<br>    logging.info(f&quot;{table_id} table not exists. Proceed without getting schema&quot;)<br>    original_schema_map = {}</pre><p>and now the only thing we need to change is to add the original_schema_map in the deduce schema at the code above</p><pre>schema_map,errors = generator.deduce_schema(input_data=df_dict,<br>                                     <strong>schema_map=original_schema_map</strong>)</pre><p>so here’s the final version of our WriteDataframeToBQ</p><pre>class WriteDataframeToBQ(beam.DoFn):</pre><pre>def __init__(self, bq_dataset, bq_table, project_id):<br>  self.bq_dataset = bq_dataset<br>  self.bq_table = bq_table<br>  self.project_id = project_id</pre><pre>def start_bundle(self):<br>  self.client = bigquery.Client()</pre><pre>def process(self, df):<br>  # table where we&#39;re going to store the data<br>  table_id = f&quot;{self.bq_dataset}.{self.bq_table}&quot;</pre><pre>  # function to help with the json -&gt; bq schema transformations<br>  generator = SchemaGenerator(input_format=&#39;dict&#39;, quoted_values_are_strings=True, keep_nulls=True)</pre><pre>  # Get original schema to assist the deduce_schema function.<br>  # If the table doesn&#39;t exist<br>  # proceed with empty original_schema_map<br>  try:<br>    table_file_name = f&quot;original_schema_{table_id}.json&quot;<br>    table = self.client.get_table(table_id)<br>    self.client.schema_to_json(table.schema, table_file_name)<br>    original_schema_map = <br>                     read_existing_schema_from_file(table_file_name)<br>  except Exception:<br>      logging.info(f&quot;{table_id} table not exists. Proceed without getting schema&quot;)<br>      original_schema_map = {}<br>  <br>  # convert dataframe to dict<br>  json_text = df.to_dict(&#39;records&#39;)</pre><pre>  # generate the new schema, we need to write it to a file<br>  # because schema_from_json only accepts json file as input<br>  schema_map, error_logs = generator.deduce_schema(<br>                                     input_data=json_text, <br>                                     schema_map=original_schema_map)<br>  bq_schema = generator.flatten_schema(schema_map)</pre><pre>  job_config = bigquery.LoadJobConfig(<br>    source_format= bigquery.SourceFormat.NEWLINE_DELIMITED_JSON,<br>    schema_update_options=[<br>                  bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION,<br>                  bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION<br>                 ],<br>    write_disposition=bigquery.WriteDisposition.WRITE_APPEND,<br>    schema=bq_schema<br>  )</pre><pre>  try:<br>    load_job = self.client.load_table_from_json(<br>                json_text,<br>                table_id,<br>                job_config=job_config,<br>            )  # Make an API request.</pre><pre>    load_job.result()  # Waits for the job to complete.<br>    if load_job.errors:<br>      logging.info(f&quot;result={load_job.error_result}&quot;)<br>      logging.info(f&quot;errors={load_job.errors}&quot;)<br>    else:<br>      logging.info(f&#39;Loaded {len(df)} rows.&#39;)</pre><pre>  except Exception as error:<br>    logging.info(f&#39;Error: {error} with loading dataframe&#39;)</pre><pre>    if load_job and load_job.errors:<br>      logging.info(f&quot;result={load_job.error_result}&quot;)<br>      logging.info(f&quot;errors={load_job.errors}&quot;)</pre><p>and that’s it! Our Dataflow job can update the BigQuery schema as we get new fields!</p><p>You can find the full code here:<br><a href="https://github.com/aFrag/PubsubToBigQuery">https://github.com/aFrag/PubsubToBigQuery</a></p><h4>Final thoughts</h4><p><strong>keep_nulls: </strong>I’m using keep_nulls = True in the SchemaGenerator because some of my PubSub messages contain empty arrays. Trying to have an empty array in the BQ schema would end up in an error. Keep nulls will append a dummy field to the array and when the actual data comes, it will remove the dummy field and add the correct ones.<br><strong>Dataframes</strong>: Instead of converting the data frame to a dict json_text = df.to_dict(‘records’) we can use the JSON directly. I’m not doing that because on the previous step I’m adding all the messages in a data frame in order to drop duplicates, keep the latest record, do some clean up etc.<br><strong>Schema File</strong>: In the final implementation we export the schema to f”original_schema_{table_id}.json” file, I haven’t tested how this is going to work if at some point we have multiple dataflow workers trying simultaneously to update the schema and they have different versions of it.<br><strong>Query BigQuery for the schema: </strong>In the above implementation, we are grouping the data in 10min windows and then inserting them into BigQuery. That’s why making an extra call to get the schema won’t add a substantial delay to our process. If we need a more real-time approach, then a proposed solution could be to try and write the incoming messages directly to BigQuery without getting the schema and if it fails, move all the failed messages to another step where we’re going to update the schema and then insert the failed data.</p><p>2021–05–18<br>I’ve updated the code because I was using the SchemaGenerator library wrong. I wasn’t aware of the read_existing_schema_from_file function and my code wasn’t generating the correct schema</p><h4>References:</h4><p><a href="https://googleapis.dev/python/bigquery/latest/usage/pandas.html">https://googleapis.dev/python/bigquery/latest/usage/pandas.html</a><br><a href="https://pypi.org/project/bigquery-schema-generator/">https://pypi.org/project/bigquery-schema-generator/</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=49b420838bac" width="1" height="1" alt=""><hr><p><a href="https://medium.com/inside-league/loading-complex-json-files-in-realtime-to-bigquery-from-pubsub-using-dataflow-and-updating-the-49b420838bac">Loading complex JSON files in RealTime to BigQuery from PubSub using Dataflow and updating the…</a> was originally published in <a href="https://medium.com/inside-league">Inside League</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Real-Time Streaming Salesforce Updates to PubSub]]></title>
            <link>https://medium.com/inside-league/real-time-streaming-salesforce-updates-to-pubsub-d9aedd5973ca?source=rss-18b8f379ecf2------2</link>
            <guid isPermaLink="false">https://medium.com/p/d9aedd5973ca</guid>
            <category><![CDATA[engineering]]></category>
            <dc:creator><![CDATA[Alex Fragotsis]]></dc:creator>
            <pubDate>Sat, 12 Dec 2020 23:16:18 GMT</pubDate>
            <atom:updated>2020-12-17T12:07:35.411Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/703/1*8BUWDfVEZgxkOIP9FRg-Jg.png" /></figure><h3>TL/DR</h3><p>The working implementation is on Third Attempt</p><h3>Intro</h3><p>A couple of months ago when we started building our data lake, one of the requirements was to try and get real-time data in. Most of our external and internal sources supported that, I had a bit more knowledge on Salesforce so I started researching how we can stream data from Salesforce in real-time.</p><p>Luckily I stumbled upon <a href="https://developer.salesforce.com/docs/atlas.en-us.change_data_capture.meta/change_data_capture/cdc_intro.htm">Salesforce Change Data Capture</a>. At a first glance, it looks good, <em>great</em>, I thought, <em>this is exactly what we need</em>. So I started building a POC.</p><p>And thus, started most of my problems. Since our entire stack is on Google, and we already have set up some streaming jobs using Dataflow (with python), I thought to try and leverage that. So I tried searching how I can build a streaming pipeline using Dataflow aaaaand…. unfortunately, is not possible…</p><p>You see Dataflow in Python has connectors to <a href="https://beam.apache.org/documentation/io/built-in/">limited Streaming Sources</a> (only Kafka and PubSub). <em>Ok</em>, I thought, <em>I will build an Unbounded Source for Salesforce myself</em>… wrong again… In Python building custom Unbounded sources is not supported.</p><h3>First attempt: Salesforce CDC and “Hacking” Dataflow</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/462/1*oabErb4hvBXHgwXpSiIP_g.png" /></figure><p>Even though it seems that I couldn’t use Dataflow, I wanted to try it anyway. So I build a pipeline and put the code which listens for messages from Salesforce inside <strong><em>ParDo</em></strong>. Surprisingly it worked! I let it run for 5–10 days without running to any issues, the streaming was working, it was pushing messages to PubSub all good.</p><pre>import argparse<br>import asyncio<br>import logging</pre><pre>import apache_beam as beam<br>from aiosfstream import SalesforceStreamingClient<br>from apache_beam.options.pipeline_options import PipelineOptions</pre><pre>async def stream_events():<br>   # connect to Streaming API<br>   password = ‘my_password’<br>   security_token = ‘my_security_token’<br>   client = SalesforceStreamingClient(<br>     consumer_key=’app_consumer_key’,<br>     consumer_secret=’app_consumer_secret’,<br>     <a href="/service/mailto:username=&#39;data+pchealth@league.com">username=’alex_test@salesforce_domain.com</a>.sandbox’,<br>     password=f’{password}{security_token}’,<br>     sandbox=True<br>   )<br>   await client.open()</pre><pre>   # subscribe to topics<br>   await client.subscribe(“/data/ContactChangeEvent”)</pre><pre>   logging.info(“waiting for messages”)<br>   # listen for incoming messages<br>   async for message in client:<br>     topic = message[“channel”]<br>     data = message[“data”]<br>     logging.info(f”{topic}: {data}”)<br>     push_data_to_pubsub(data) #<a href="https://googleapis.dev/python/pubsub/latest/index.html">sample implementation</a></pre><pre>class ReadData(beam.DoFn):<br>   def process(self, nothing):<br>     loop = asyncio.new_event_loop()<br>     asyncio.set_event_loop(loop)<br>     loop.run_until_complete(stream_events())</pre><pre>class SalesforceStreamingOptions(PipelineOptions):</pre><pre>def run(argv=None):</pre><pre>  with beam.Pipeline(options=pipeline_options) as pipeline:<br>   (pipeline<br>    | ‘Start’ &gt;&gt; beam.Create([None])<br>    | ‘Parse Data’ &gt;&gt; beam.ParDo(ReadData())<br>   )</pre><pre>if __name__ == ‘__main__’:<br>   run()</pre><p>But here are a few of the downsides.</p><ul><li>We can’t use <strong>yield</strong> to push any messages downstream because the streaming happens on a different thread (see <em>ReadData</em> method)</li><li>All the code has to be written inside one block of ParDo, which is not ideal</li><li>The code inside the ParDo is supposed to manipulate one element at a time, modify the message and then pass it on to the next step, so even though the code works fine, it’s not supposed to be used like that</li><li>That’s why if you see the logs are flooded with messages like this.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IA9eaMggg5s3SW7kRtzdSw.png" /></figure><blockquote>Dataflow thinks that we’re processing the same element for 25 hours straight…</blockquote><p>Soo, after that I went back to brainstorming what would be the next thing to try. After taking a look at all the available GCP products (and eliminated a few like CloudFunctions and CloudRun), I thought that the only way is to set up a microservice that will run on our already-set-up GKE cluster which will do only one thing, listen for messages from salesforce and push them to PubSub. (the <em>stream_events</em> function from above)</p><p>It might sound like a good idea but here comes the second requirement. Salesforce CDC pushes only the <a href="https://developer.salesforce.com/docs/atlas.en-us.change_data_capture.meta/change_data_capture/cdc_message_structure.htm">changed fields</a> from the table we are watching, we wanted to get the whole row, and as much as I searched, the only way to achieve this is to make a get call back to Salesforce API on every event message we got.</p><blockquote>In order to get the whole row on every change event, we would have to make a get call back to Salesforce after every change event</blockquote><p>In theory that could work too, but it wouldn’t be scalable and we would hit the API limits pretty soon. So back to brainstorming again…</p><p>At this point, I started asking people from other teams if they have any ideas, and this is where the solution came from. Some people from the backend/DevOps team proposed that instead of listening for messages and hitting back the Salesforce API, we could push them out of Salesforce. They proposed writing an <a href="https://developer.salesforce.com/docs/atlas.en-us.change_data_capture.meta/change_data_capture/cdc_trigger_example.htm">Apex Trigger</a> which should push data to a cloud function which was going to push the data to PubSub</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/523/1*heFC13Gew_3PtwurktDUjQ.png" /></figure><p>Turns out, we had already set up a repo with Apex code I could leverage. I could write my Apex Trigger there and on every change to an underlying table we would get an event with the changed rows which we could push to a cloud function and from there push them to PubSub. We already had set up CI/CD on the repo for deploying the code and querying Salesforce Database from within the APEX Script doesn’t count towards the API Limits!! So the only thing remaining was to write the code. The downside? Write Apex Code!</p><blockquote>Querying Salesforce Database from Apex doesn’t count towards the API Limits</blockquote><h3>Second attempt: APEX Triggers and CloudFunction</h3><p>So here I am again, searching for how to write APEX Triggers. Luckily I know Java so all the code looked familiar but one of the negatives of APEX is that we can’t use standard Java Libraries. So in my POC in order to call the cloud function from within Salesforce, I used <a href="https://trailhead.salesforce.com/content/learn/modules/apex_integration_services/apex_integration_rest_callouts">Apex Callouts</a>.</p><p>The POC was successful but thinking about production we don’t want anyone with the URL to be able to call our cloud function, so we want to find a way to restrict access by authenticating to GCP (using a service account) and then call the function.</p><p>But wait…. If we can authenticate to Google with a service account, why not push directly to PubSub?</p><h3>Third attempt: APEX Triggers and PubSub</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/333/1*Ws1ONOLkoGxNOd7hjM0heA.png" /></figure><p>So here we are, third and last attempt. Directly pushing data from Salesforce to PubSub. After researching online and finding many useful articles and especially one <a href="https://cloud.google.com/solutions/calling-protected-cloud-functions">written by Google’s Engineers</a> I was able to make it work.</p><p>It’s a bit complicated because we need to set up some things in Salesforce before we can try our code, but I’ll try to explain step-by-step</p><h4>Salesforce Setup</h4><p><strong>Generate JKS key for the Service Account</strong></p><p>First things first, assuming we have set up PubSub and we have a service account with publishing access to a topic. We need to generate a key and import it to Salesforce. Unfortunately, Salesforce only accepts jks keys so we’ll have to make one.</p><ol><li>In the Google Cloud Console, navigate to <strong>Identity</strong> -&gt; <strong>Service Accounts</strong></li><li>Find the service account you’re going to use to push data to PubSub, click the 3 little dots under the “Actions” column and hit <strong>Create Key</strong></li><li>In the Popup menu select <strong>P12</strong> and click <strong>Create</strong> (The default password is notasecret)</li><li>Once you hit Create the P12 file will be downloaded locally</li></ol><p>Now we need to transform the P12 key to jks. In order to do that we’ll use the cloud shell.</p><ol><li>In the Google Cloud Console click <strong>Activate Cloud Shell</strong> on the toolbar</li><li>Click <strong>More</strong> select <strong>Upload File</strong> and select the P12 file you’ve just downloaded</li><li>Rename the key to a friendlier name so you can use it easier</li></ol><pre>mv ${DEVSHELL_PROJECT_ID}-NNNNNN.p12 sf-pubsub-sa-key.p12</pre><p>4. Now run the following command to generate a jks version of the key</p><pre>keytool \<br>    -importkeystore \<br>    -srckeystore $HOME/sf-pubsub-sa-key.p12 \<br>    -destkeystore $HOME/salesforce.jks \<br>    -srcstoretype pkcs12 \<br>    -srcstorepass notasecret \<br>    -deststorepass notasecret \<br>    -deststoretype jks \<br>    -destalias google_cloud \<br>    -srcalias privatekey</pre><p>5. Now click <strong>More</strong> and click <strong>Download File</strong><br>6. In the popup window enter salesforce.jks and click Download<br>7. This will download the salesforce.jksfile to your local computer</p><p><strong>Import Service Account Key to Salesforce</strong></p><p>Now that we have the jks key we need to import it to Salesforce.</p><ol><li>Login to Salesforce and in Setup use the Quick Find and search for <strong>Identity Provider</strong></li><li>Click <strong>Enable Identity Provider</strong> and then click <strong>Save</strong> to accept the certificate.</li><li>Again using Quick Find, search for <strong>Certificate and Key Management</strong></li><li>Click <strong>Import from Keystore</strong></li><li>Click <strong>Choose File </strong>and select the jks file you downloaded earlier</li><li>In the Keystore Password enter notasecret</li><li>Click <strong>Save</strong></li><li>Make sure that the newly added certificate is labelled as <strong>google_cloud </strong>because that’s what we’re going to use later in the Apex script</li></ol><p><strong>Setting up a Custom Metadata Type Object</strong></p><p>This part is not necessary but it’s better to use a custom metadata object to store the <em>PubSub URL</em>, the<em> GCP Authentication URL</em> and the <em>service account email </em>instead of hardcoding them into code. This is also helpful because with the same code we can access different URLs and service account emails in a sandbox and in prod.</p><p>The following code creates the Custom Object <br><a href="https://github.com/aFrag/SalesforceToPubSub/tree/main/force-app/main/default/objects/GCP_Key__mdt">force-app/main/default/objects/GCP_Key__mdt/GCP_Key__mdt.object-meta.xml</a></p><pre>&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;<br>&lt;CustomObject xmlns=&quot;<a href="http://soap.sforce.com/2006/04/metadata">http://soap.sforce.com/2006/04/metadata</a>&quot;&gt;<br>    &lt;label&gt;GCP Key&lt;/label&gt;<br>    &lt;pluralLabel&gt;GCP Keys&lt;/pluralLabel&gt;<br>    &lt;visibility&gt;Public&lt;/visibility&gt;<br>&lt;/CustomObject&gt;</pre><p>And with the following metadata file, we add an entry to the object we just created<br><a href="https://github.com/aFrag/SalesforceToPubSub/tree/main/force-app/main/default/customMetadata">force-app/main/default/customMetadata/GCP_Key.Stage_GCP_PubSub_Cases.md-meta.xml</a></p><pre>&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;<br>&lt;CustomMetadata xmlns=&quot;<a href="http://soap.sforce.com/2006/04/metadata">http://soap.sforce.com/2006/04/metadata</a>&quot; xmlns:xsi=&quot;<a href="http://www.w3.org/2001/XMLSchema-instance">http://www.w3.org/2001/XMLSchema-instance</a>&quot; xmlns:xsd=&quot;<a href="http://www.w3.org/2001/XMLSchema">http://www.w3.org/2001/XMLSchema</a>&quot;&gt;<br>    &lt;label&gt;Stage_GCP_PubSub_Cases&lt;/label&gt;<br>    &lt;protected&gt;false&lt;/protected&gt;<br>    &lt;values&gt;<br>        &lt;field&gt;Client_Email__c&lt;/field&gt;<br>        &lt;value xsi:type=&quot;xsd:string&quot;&gt;<a href="/service/mailto:stage-salesforce-alex@alex-stage-project.iam.gserviceaccount.com">stage-salesforce-alex@alex-stage-project.iam.gserviceaccount.com</a>&lt;/value&gt;<br>    &lt;/values&gt;<br>    &lt;values&gt;<br>        &lt;field&gt;Pubsub_Endpoint__c&lt;/field&gt;<br>        &lt;value xsi:type=&quot;xsd:string&quot;&gt;<a href="https://pubsub.googleapis.com/v1/projects/alex-stage-project/topics/stage-alex-salesforce-cases:publish">https://pubsub.googleapis.com/v1/projects/alex-stage-project/topics/stage-alex-salesforce-cases:publish</a>&lt;/value&gt;<br>    &lt;/values&gt;<br>    &lt;values&gt;<br>        &lt;field&gt;Token_Endpoint__c&lt;/field&gt;<br>        &lt;value xsi:type=&quot;xsd:string&quot;&gt;<a href="https://www.googleapis.com/oauth2/v4/token">https://www.googleapis.com/oauth2/v4/token</a>&lt;/value&gt;<br>    &lt;/values&gt;<br>&lt;/CustomMetadata&gt;</pre><p>Notice in the code</p><ul><li>the service account email should be the same as the one we have on the service account key we generated and uploaded earlier</li><li>The URL we for authenticating to Google is always <a href="https://www.googleapis.com/oauth2/v4/token">https://www.googleapis.com/oauth2/v4/token</a></li><li>And the PubSub URL would be<br><a href="https://pubsub.googleapis.com/v1/projects/league-datalake/topics/prod-league-salesforce-tickets-in:publish">https://pubsub.googleapis.com/v1/</a>{pubsub_topic_url}:publish<br>For Example: <br><a href="https://pubsub.googleapis.com/v1/projects/league-datalake/topics/prod-league-salesforce-tickets-in:publish">https://pubsub.googleapis.com/v1/projects/alex-test-project/topics/salesforce-cases:publish</a></li></ul><p><strong>Remote Site</strong></p><p>In order for Salesforce to be able to make external calls to Google APIs we need to add the URLs as <strong>Remote Site. </strong>In our case, we need to add the Google Auth Url and the PubSub Url<br><a href="https://github.com/aFrag/SalesforceToPubSub/blob/main/force-app/main/default/remoteSiteSettings/GCP_Auth.remoteSite-meta.xml">force-app/main/default/remoteSiteSettings/GCP_Auth.remoteSite-meta.xml</a></p><pre>&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;<br>&lt;RemoteSiteSetting xmlns=&quot;http://soap.sforce.com/2006/04/metadata&quot;&gt; <br>      &lt;fullName&gt;GCP_Auth&lt;/fullName&gt;    <br>      &lt;description&gt;To authenticate to google services&lt;/description&gt;    <br>      &lt;disableProtocolSecurity&gt;false&lt;/disableProtocolSecurity&gt;<br>      &lt;isActive&gt;true&lt;/isActive&gt;<br>      &lt;url&gt;https://accounts.google.com&lt;/url&gt;<br>&lt;/RemoteSiteSetting&gt;</pre><p><a href="https://github.com/aFrag/SalesforceToPubSub/blob/main/force-app/main/default/remoteSiteSettings/GCP_PubSub.remoteSite-meta.xml">force-app/main/default/remoteSiteSettings/GCP_PubSub.remoteSite-meta.xml</a></p><pre>&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;<br>&lt;RemoteSiteSetting xmlns=&quot;http://soap.sforce.com/2006/04/metadata&quot;&gt;<br>    &lt;fullName&gt;GCP_PubSub&lt;/fullName&gt;<br>    &lt;description&gt;Used to publish messages to PubSub&lt;/description&gt;<br>    &lt;disableProtocolSecurity&gt;false&lt;/disableProtocolSecurity&gt;<br>    &lt;isActive&gt;true&lt;/isActive&gt;<br>    &lt;url&gt;https://pubsub.googleapis.com&lt;/url&gt;<br>&lt;/RemoteSiteSetting&gt;</pre><p><strong>Cache Partition</strong></p><p>The final thing we need to set up is the cache. When we authenticate to Google we’ll get back a bearer token, which then we’ll add to our headers to push data to PubSub. This token expires after 1 hour. So for this hour, we can re-use the old token.<br>Setting up the cache partition is similar</p><p><a href="https://github.com/aFrag/SalesforceToPubSub/blob/main/force-app/main/default/cachePartitions/gcpPartition.cachePartition-meta.xml">force-app/main/default/cachePartitions/gcpPartition.cachePartition-meta.xml</a></p><pre>&lt;?xml version=”1.0&quot; encoding=”UTF-8&quot;?&gt;<br>&lt;PlatformCachePartition xmlns=”http://soap.sforce.com/2006/04/metadata&quot;&gt;<br>  &lt;description&gt;Partition for storing GCP Access token &lt;/description&gt;<br>  &lt;isDefaultPartition&gt;false&lt;/isDefaultPartition&gt;<br>  &lt;masterLabel&gt;gcpPartition&lt;/masterLabel&gt;<br>  &lt;platformCachePartitionTypes&gt;<br> ​​   &lt;allocatedCapacity&gt;0&lt;/allocatedCapacity&gt;<br>    &lt;cacheType&gt;Session&lt;/cacheType&gt;<br>  &lt;/platformCachePartitionTypes&gt;<br>  &lt;platformCachePartitionTypes&gt;<br> ​​   <strong>&lt;allocatedCapacity&gt;1&lt;/allocatedCapacity&gt;</strong><br>    &lt;cacheType&gt;Organization&lt;/cacheType&gt;<br>  &lt;/platformCachePartitionTypes&gt;​<br>&lt;/PlatformCachePartition&gt;</pre><p>In the above code, we allocate 1MB to the <strong>gcpPartition</strong>. Since we only plan to store one token it should be enough.</p><h4><strong>Apex Code</strong></h4><p>Ok Finally we have finished with the Salesforce setup, now we can move to write the actual Apex Code. For this example, we’ll create 1 trigger and 2 helper functions.</p><p><strong>CaseTrigger.trigger</strong></p><p>Our trigger is pretty simple. We want to get updates after every insert, update, delete and undelete on the Case table, we serialize the data to a JSON object and we use <strong>System.enqueueJob</strong> to start the background service which is going to push the data to PubSub.</p><pre>trigger CaseTrigger on Case (after insert,after update,after delete,after undelete) {<br>    if(Trigger.isAfter){<br>        List&lt;Case&gt; cases;</pre><pre>        if(Trigger.isDelete){<br>            cases = trigger.old;<br>        }else{<br>            cases = trigger.new;<br>        } <br>        <br>        String serialisedCases = JSON.serialize(cases);<br>        PubSub gcpService = new PubSub(serialisedCases, String.valueOf(Trigger.operationType), PubSub.GCP_CASES_METADATA_NAME);<br>        ID jobID = System.enqueueJob(gcpService);<br>    }<br>}</pre><p><strong>PubSub.cls</strong></p><p>For the PubSub class, we use the Queueable interface to be able to run the code as a background service. This is because making Callouts is not allowed in the main thread, also since it’s running in the background it won’t affect Salesforce’s performance</p><pre>public class PubSub implements Queueable, Database.AllowsCallouts {<br>  public static final String GCP_CASES_METADATA_NAME = &#39;GCP_PubSub_Cases&#39;;</pre><pre>  // Serialised data coming from triggers<br>  private String serializedData;</pre><pre>  // Action to be performed in GCP, can be &#39;create&#39; in this example<br>  private String action;</pre><pre>  // Metadata name in order to get the appropriate entry from sf   metadata<br>  private String metadata_name;<br> <br>  // Constructor    <br>  public PubSub(String serializedData, String action, String metadata_name) {<br>    this.serializedData = serializedData;<br>    this.action = action;<br>    this.metadata_name = metadata_name;<br>  }</pre><pre>  @TestVisible<br>  private void pushDataToPubsub(String token, String pubsubEndpoint) {<br>  List&lt;SObject&gt; deserialisedData = (List&lt;SObject&gt;) JSON.deserialize(serializedData, List&lt;SObject&gt;.class);</pre><pre>  if(!deserialisedData.get(0) instanceof Case){<br>       throw new GCPServiceAccountException(&#39;The object you are trying to push to PubSub is not supported&#39;);<br>    }</pre><pre>  PubsubRequest requestMessage = new PubsubRequest(deserialisedData, this.action);</pre><pre>  HTTP http = new HTTP();<br>  HTTPRequest req = new HTTPRequest();<br>  req.setHeader(&#39;Content-Type&#39;,&#39;application/json&#39;);<br>  req.setHeader(&#39;Authorization&#39;, &#39;Bearer &#39; + token);<br>  req.setEndpoint(pubsubEndpoint);<br>  req.setMethod(&#39;POST&#39;);<br>  req.setBody(JSON.serialize(requestMessage));</pre><pre>  HTTPResponse response=new HttpResponse();<br>  System.debug(&#39;POST request to &#39; + pubsubEndpoint);<br>  if (!Test.isRunningTest()) {<br>      response = http.send(req);<br>  }<br>        <br>  if (response.getStatusCode() == 200) {<br>      System.debug(pubsubEndpoint + &#39; called successfully&#39;);<br>  } else {<br>      System.debug(LoggingLevel.ERROR, &#39;Error occured &#39; + response.getStatus() + &#39; &#39; + response.getStatusCode());<br>  }<br>}</pre><pre>public void execute(QueueableContext context) {<br>    GoogleUtils utils = new GoogleUtils(this.metadata_name);<br>    <br>    if (utils.get_token() != null) {<br>      this.pushDataToPubsub(utils.get_token(), <br>                            utils.get_audience());<br>    }<br> }</pre><pre>public class GCPServiceAccountException extends Exception {}</pre><pre>}</pre><p><strong>GoogleUtils.cls</strong><br>The following function will query the custom Metadata Object we made and get us the GCP Key object.<br>Then we can use the following function to authenticate to Google</p><pre>public class GoogleUtils {</pre><pre> private String authToken = &#39;&#39;;<br> private String audience = &#39;&#39;;</pre><pre> public GoogleUtils(String metadata_name){<br>   GCP_Key__mdt serviceAccount = getGCPServiceAccount(metadata_name);<br>   audience = serviceAccount.Pubsub_Endpoint__c;<br>   authToken = getGCPAuthToken(serviceAccount);<br> }<br>    <br> public String get_token(){<br>   return authToken;<br> }<br>    <br> public String get_audience(){<br>   return audience;<br> }</pre><pre><strong>// For authenticating to Google and saving the token to cache<br></strong>public String getGCPAuthToken(GCP_Key__mdt serviceAccount) {<br>  String cachedAccessToken = (String)<br>                              Cache.Org.get(CACHE_ACCESS_TOKEN_KEY);<br>  if (cachedAccessToken != null) {<br>     System.debug(&#39;Returning cached access token&#39;);<br>     return cachedAccessToken;<br>  } else {<br>     GoogleAuthResponse result = new GoogleAuthResponse();</pre><pre>     Auth.JWT jwt = new Auth.JWT();<br>     jwt.setAud(serviceAccount.Token_Endpoint__c);<br>     jwt.setIss(serviceAccount.client_email__c);</pre><pre>     // Additional claims to set scope<br>     Map&lt;String, Object&gt; claims = new Map&lt;String, Object&gt;();<br>     claims.put(&#39;scope&#39;, &#39;<a href="https://www.googleapis.com/auth/pubsub&#39;">https://www.googleapis.com/auth/pubsub&#39;</a>);<br>     jwt.setAdditionalClaims(claims);</pre><pre>     // Create the object that signs the JWT bearer token<br>     Auth.JWS jws = new Auth.JWS(jwt, &#39;<strong>google_cloud</strong>&#39;);</pre><pre>     // Get the resulting JWS in case debugging is required<br>     String token = jws.getCompactSerialization();</pre><pre>     //Set the token endpoint that the JWT bearer token is posted to<br>     String tokenEndpoint = serviceAccount.Token_Endpoint__c;</pre><pre>     Auth.JWTBearerTokenExchange bearer = new<br>                   Auth.JWTBearerTokenExchange(tokenEndpoint, jws);</pre><pre>     if (!Test.isRunningTest()) {<br>           System.HttpResponse response = bearer.getHTTPResponse();<br>           result =(GoogleAuthResponse) <br>     JSON.deserialize(response.getBody(), GoogleAuthResponse.class);<br>     } else {<br>           result.access_token = &#39;IN TEST&#39;;<br>     }<br>     cacheAccessToken(result.access_token);<br>     return result.access_token;<br>  }<br>}</pre><pre><strong>// For getting the service account metadata from the custom object we created earlier</strong><br>public GCP_Key__mdt getGCPServiceAccount(String metadata_name) {<br>  List&lt;GCP_Key__mdt&gt; gcp_sa_keys = new List&lt;GCP_Key__mdt&gt;();<br>  gcp_sa_keys = [SELECT Client_Email__c, <br>                      Pubsub_Endpoint__c, <br>                      Token_Endpoint__c<br>                 FROM GCP_Key__mdt<br>                 WHERE MasterLabel = :metadata_name];<br>  if (gcp_sa_keys.size() &gt; 0) {<br>      return gcp_sa_keys[0];<br>  } else {<br>    throw new GCPServiceAccountException(&#39;Cannot find GCP Service<br>                                                     Account Keys&#39;);<br>  }<br>}</pre><pre><strong>// For the cache<br></strong>private final static String CACHE_ACCESS_TOKEN_KEY = &#39;local.gcpPartition.googleCloudAccessToken&#39;;</pre><pre>public static void cacheAccessToken(String accessToken) {<br>    // Cache the access token in the default org cache with a TTL of 30 seconds less than its expiration<br>   Cache.Org.put(CACHE_ACCESS_TOKEN_KEY, accessToken, 3600 - 30);<br>}<br>   <br>public static Boolean clearAccessToken() {<br>    // Method to clear the cached token from our local cache<br>    return Cache.Org.remove(CACHE_ACCESS_TOKEN_KEY);<br>}<br>}</pre><p>And that’s it. Our trigger should work fine. Looking at the Logs in the Salesforce Developer Console we’re able to see that our post called successfully</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_kEKrVWYp-gOEMEX34lXAg.png" /></figure><p>and the message arrived in PubSub</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TCQZBn2PTDiqbkhaoWKMqQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/256/1*rtBEh9Jn6jSnUv9KOFvtQQ.gif" /></figure><p>You can find the full code here: <a href="https://github.com/aFrag/SalesforceToPubSub">https://github.com/aFrag/SalesforceToPubSub</a></p><h3>Finally</h3><ul><li>When I started this POC I had no idea what direction to go, I tried, failed and tried again</li><li>You never know where the solution will come from. On my occasion, it was brainstorming with people from completely different teams who had knowledge that I didn’t.</li><li>Just because it’s working doesn’t mean it’s good (my First attempt with Dataflow)</li></ul><h4>References</h4><ol><li><a href="https://cloud.google.com/solutions/calling-protected-cloud-functions">https://cloud.google.com/solutions/calling-protected-cloud-functions</a></li><li><a href="https://github.com/GoogleCloudPlatform/salesforce-cloud-functions-crm-tutorial">https://github.com/GoogleCloudPlatform/salesforce-cloud-functions-crm-tutorial</a></li><li><a href="https://trailhead.salesforce.com/en/content/learn/modules/apex_integration_services/apex_integration_rest_callouts">https://trailhead.salesforce.com/en/content/learn/modules/apex_integration_services/apex_integration_rest_callouts</a></li><li><a href="https://salesforce.stackexchange.com/questions/13301/connect-apex-and-google-api-using-jwt-to-retrieve-oauth-2-0-token/71442#71442">https://salesforce.stackexchange.com/questions/13301/connect-apex-and-google-api-using-jwt-to-retrieve-oauth-2-0-token/71442#71442</a></li><li><a href="https://medium.com/@kumar385/google-pubsub-rest-api-61d96d3deae9">https://medium.com/@kumar385/google-pubsub-rest-api-61d96d3deae9</a></li><li><a href="https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_platform_cache_org_examples.htm">https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_platform_cache_org_examples.htm</a></li><li><a href="https://developer.salesforce.com/docs/atlas.en-us.change_data_capture.meta/change_data_capture/cdc_trigger_intro.htm">https://developer.salesforce.com/docs/atlas.en-us.change_data_capture.meta/change_data_capture/cdc_trigger_intro.htm</a></li><li><a href="https://developer.salesforce.com/docs/atlas.en-us.change_data_capture.meta/change_data_capture/cdc_trigger_example.htm">https://developer.salesforce.com/docs/atlas.en-us.change_data_capture.meta/change_data_capture/cdc_trigger_example.htm</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d9aedd5973ca" width="1" height="1" alt=""><hr><p><a href="https://medium.com/inside-league/real-time-streaming-salesforce-updates-to-pubsub-d9aedd5973ca">Real-Time Streaming Salesforce Updates to PubSub</a> was originally published in <a href="https://medium.com/inside-league">Inside League</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Pyspark: How to Modify a Nested Struct Field]]></title>
            <link>https://medium.com/swlh/pyspark-how-to-modify-a-nested-struct-field-8105ebe83d09?source=rss-18b8f379ecf2------2</link>
            <guid isPermaLink="false">https://medium.com/p/8105ebe83d09</guid>
            <category><![CDATA[struct]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[nested]]></category>
            <category><![CDATA[spark]]></category>
            <dc:creator><![CDATA[Alex Fragotsis]]></dc:creator>
            <pubDate>Sat, 29 Aug 2020 17:47:03 GMT</pubDate>
            <atom:updated>2020-09-04T22:12:27.061Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zGszmxI9R46bDK6AODVB4w.png" /></figure><p>In our adventures trying to build a data lake, we are using dynamically generated spark cluster to ingest some data from MongoDB, our production database, to BigQuery. In order to do that, we use PySpark data frames and since mongo doesn’t have schemas, we try to infer the schema from the data.</p><pre>collection_schema = spark.read.format(“mongo”) \ <br>.option(“database”, db) \ <br>.option(“collection”, coll) \ <br>.option(‘sampleSize’, 50000) \ <br>.load() \ <br>.schema </pre><pre>ingest_df = spark.read.format(“mongo”) \ <br>.option(“database”, db) \ <br>.option(“collection”, coll) \ .load(schema=fix_spark_schema(collection_schema))</pre><p>Our <strong>fix_spark_schema </strong>method just converts NullType columns to String.</p><p>In the <strong>users</strong> collection, we have the <strong>groups</strong> field, which is an array, because users can join multiple groups.</p><pre>root<br> |-- groups: array (nullable = true)<br> |    |-- element: struct (containsNull = true)<br> |    |    |-- programs: struct (nullable = true)<br> |    |    |    |-- <strong>**{ program id }**</strong>: struct (nullable = true)<br> |    |    |    |    |-- Date: timestamp (nullable = true)<br> |    |    |    |    |-- Name: string (nullable = true)<br> |    |    |    |    |-- Some_Flags: struct (nullable = true)<br> |    |    |    |    |    |-- abc: boolean (nullable = true)<br> |    |    |    |    |    |-- def: boolean (nullable = true)<br> |    |    |    |    |    |-- ghi: boolean (nullable = true)<br> |    |    |    |    |    |-- xyz: boolean (nullable = true)</pre><p>Also, each different group has some different programs the users can join. So under the programs, we store a JSON with keys the program ids the user has joined and values some extra data about the date they joined etc. The data looks like this</p><pre>“groups” : [<br> {<br>  <em>… some other fields …</em><br>  “programs” : {<br>     “123c12b123456c1d76a4f265f10f20a0” : {<br>        “name” : “test_program_1”, <br>        “some_flags” : {<br>           “abc” : true, <br>           “def” : true, <br>           “ghi” : false, <br>           “xyz” : true<br>        }, <br>        “date” : ISODate(“2019–11–16T03:29:00.000+0000”)<br>     }<br> }<br>]</pre><p>As a result of the above, BigQuery creates a new column for each <strong>program_id</strong> and we end up with hundreds of columns, most of them empty for most of the users. So, how can we fix that? We can convert <strong>programs</strong> from a struct to string and store the whole json in there. That would create some extra friction if someone wants to access those fields, but it would make our columns much cleaner.</p><p><strong>Attempt 1:</strong></p><p>So, if the field wasn’t nested we could easily just cast it to string.</p><pre>ingest_df = ingest_df.withColumn(&quot;groups.programs&quot;, col(&quot;groups.programs&quot;).cast(&#39;string&#39;))</pre><p>but since it’s nested this doesn’t work. The following command works only for root-level fields, so it could work if we wanted to convert the whole <strong>groups</strong> field, or move <strong>programs</strong> at the root level</p><pre>ingest_df = ingest_df.withColumn(&quot;programs_json&quot;, col(&quot;groups.programs&quot;).cast(&#39;string&#39;))</pre><p><strong>Attempt 2:</strong></p><p>After a lot of research and many different tries. I realized that if we want to change the type, edit, rename, add or remove a nested field we need to modify the schema. The steps we have to follow are these:</p><ol><li>Iterate through the schema of the nested Struct and make the changes we want</li><li>Create a JSON version of the root level field, in our case <strong>groups,</strong> and name it for example <strong>groups_json </strong>and drop <strong>groups</strong></li><li>Then convert the <strong>groups_json</strong> field to <strong>groups</strong> again using the modified schema we created in step 1.</li></ol><p>If we know the schema and we’re sure that it’s not going to change, we could hardcode it but … we can do better. We can write (search on <a href="https://stackoverflow.com/questions/45061190/dropping-nested-column-of-dataframe-with-pyspark/48906217#48906217">StackOverflow</a> and modify) a dynamic function that would iterate through the whole schema and change the type of the field we want. The following method would convert the <strong>fields_to_change</strong> into Strings, but you can modify it to whatever you want</p><pre>def change_nested_field_type(schema, fields_to_change, parent=&quot;&quot;):<br>  new_schema = []<br><br>  if isinstance(schema, StringType):<br>      return schema<br><br>  for field in schema:<br>      full_field_name = field.name<br><br>      if parent:<br>          full_field_name = parent + &quot;.&quot; + full_field_name<br><br>      if full_field_name not in fields_to_change:<br>          if isinstance(field.dataType, StructType):<br>              inner_schema = change_nested_field_type(field.dataType, fields_to_change, full_field_name)<br>              new_schema.append(StructField(field.name, inner_schema))<br>          elif isinstance(field.dataType, ArrayType):<br>              inner_schema = change_nested_field_type(field.dataType.elementType, fields_to_change, full_field_name)<br>              new_schema.append(StructField(field.name, ArrayType(inner_schema)))<br>          else:<br>              new_schema.append(StructField(field.name, field.dataType))<br>      else:<br>          # Here we change the field type to String<br>          <strong>new_schema.append(StructField(field.name, StringType()))</strong><br><br>  return StructType(new_schema)</pre><p>and now we can do the conversion like this:</p><pre>new_schema = ArrayType(change_nested_field_type(df.schema[&quot;groups&quot;].dataType.elementType, [&quot;programs&quot;]))<br>df = df.withColumn(&quot;groups_json&quot;, to_json(&quot;groups&quot;)).drop(&quot;groups&quot;)<br>df = df.withColumn(&quot;groups&quot;, from_json(&quot;groups_json&quot;, new_schema)).drop(&quot;groups_json&quot;)</pre><p>and voila! <strong>groups.programs</strong> is converted to a string.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8105ebe83d09" width="1" height="1" alt=""><hr><p><a href="https://medium.com/swlh/pyspark-how-to-modify-a-nested-struct-field-8105ebe83d09">Pyspark: How to Modify a Nested Struct Field</a> was originally published in <a href="https://medium.com/swlh">The Startup</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Schedule Dataflow Templates with Airflow]]></title>
            <link>https://alexfragotsis.medium.com/schedule-dataflow-templates-with-airflow-1422ecfea677?source=rss-18b8f379ecf2------2</link>
            <guid isPermaLink="false">https://medium.com/p/1422ecfea677</guid>
            <category><![CDATA[airflow]]></category>
            <category><![CDATA[schedule]]></category>
            <category><![CDATA[template]]></category>
            <category><![CDATA[pipeline]]></category>
            <category><![CDATA[dataflow]]></category>
            <dc:creator><![CDATA[Alex Fragotsis]]></dc:creator>
            <pubDate>Sat, 04 Jul 2020 19:28:03 GMT</pubDate>
            <atom:updated>2020-07-04T19:28:03.123Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7eqzO-_tG03Zmr9d8EvWqw.jpeg" /></figure><p>Ok, so, we’ve written our Dataflow Template with <a href="https://medium.com/@alexanderfragotsis/transform-json-to-csv-from-google-bucket-using-a-dataflow-python-pipeline-92906c87cc96">Python</a>, now what? We want to schedule it to run daily and we’re going to use Airflow for that.</p><p>The first thing we want, for security reasons, is to keep service accounts separate. In the previous <a href="https://medium.com/@alexanderfragotsis/transform-json-to-csv-from-google-bucket-using-a-dataflow-python-pipeline-92906c87cc96">post</a>, we’ve created a service account in order to generate the template and run the jobs. Now we need a new service account in order to trigger new dataflow jobs. <br>The new service account needs to have <strong><em>Dataflow Developer </em></strong>and<em> </em><strong><em>Cloud Dataflow Service Agent </em></strong>permissions. We’ll call this service account <strong><em>dataflow_python_runner.</em></strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5BuusNozF3khs5ugVNKJfA.png" /></figure><p>Now that we have all service accounts in place, here’s how it works.</p><ul><li>In the VM with airflow, we’ll set up the <strong><em>dataflow-python-runner</em></strong> as the default service account. This account should minimum access to our Google project. In this case, we want only to give the Dataflow Service Agent <a href="https://cloud.google.com/dataflow/docs/concepts/access-control">role</a>, but because airflow runs some extra commands, we also need to grant the Dataflow Developer role or create a new custom role granting only the specific permissions we want.</li><li><em>In airflow with the </em><strong><em>serviceAccountEmail</em></strong> field, we tell dataflow, to run this job as <strong>dataflow-python-test. </strong>This service account should have permissions to fully run a dataflow job, as well as any other access permissions e.g. to buckets.</li></ul><blockquote>So dataflow-python-runner is the service account that triggers the job, and dataflow-python-test is the service account that runs the job.</blockquote><p>If for example, you have data into a non-public bucket that this job needs to access, it’s the <strong><em>dataflow-python-test</em></strong> account that needs the read permissions and not the runner.</p><p>Now on the airflow code, we just use the DataflowTemplateOperator to trigger the template we already have.</p><pre>job = DataflowTemplateOperator(<br>    task_id=&#39;alex_test_job&#39;,<br>    template=&quot;<strong>gs://FULL_PATH_TO_TEMPLATE</strong>&quot;,<br>    job_name=&#39;<strong>alex_job_test_from_airflow</strong>&#39;,<br>    dataflow_default_options={<br>        &quot;project&quot;: &quot;<strong>MY_PROJECT_ID</strong>&quot;,<br>        &quot;stagingLocation&quot;: &quot;<strong>gs://FULL_PATH_TO_STAGING_FOLDER</strong>&quot;,<br>        &quot;tempLocation&quot;: &quot;<strong>gs://FULL_PATH_TO_TEMP_FOLDER</strong>&quot;,<br>        &quot;serviceAccountEmail&quot;: &quot;<strong><em>dataflow-python-runner@{project_id}.iam.gserviceaccount.com</em></strong>&quot;<br>)</pre><p>And the airflow interface</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/618/1*TItkrF1PgOyL0ENgVMJqAQ.png" /></figure><p>Now that the task is ready to run, we can run a test command on airflow</p><pre>airflow test dataflow_jobs alex_test_job 2020–07–04</pre><p>And we can see in Google Console in the Dataflow Jobs page if we set up all the variables correctly. If you want to be sure about the permissions, try removing/adding some permissions from the service accounts and see what are the errors.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/890/1*A_cvtVpnRKN7suM4oDnyag.png" /></figure><p>Also if you click on the job, on the bottom left you can see the pipeline options. All the fields we set on the airflow task in the <strong><em>dataflow_default_options</em></strong> field should be there. If you misspell a field it won’t show up here.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/548/1*wU2gkHcdEa1MeSM4YfqfuA.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1422ecfea677" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Transform JSON to CSV from Google bucket using a Dataflow Python pipeline]]></title>
            <link>https://medium.com/analytics-vidhya/transform-json-to-csv-from-google-bucket-using-a-dataflow-python-pipeline-92906c87cc96?source=rss-18b8f379ecf2------2</link>
            <guid isPermaLink="false">https://medium.com/p/92906c87cc96</guid>
            <category><![CDATA[apache-beam]]></category>
            <category><![CDATA[json]]></category>
            <category><![CDATA[pipeline]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[dataflow]]></category>
            <dc:creator><![CDATA[Alex Fragotsis]]></dc:creator>
            <pubDate>Sun, 31 May 2020 19:56:47 GMT</pubDate>
            <atom:updated>2020-09-06T14:11:22.447Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iKZmpDUuJAVEFayHwLhCXg.gif" /></figure><p>In this article, we will try to transform a JSON file into a CSV file using dataflow and python</p><p>First, we’ll need a service account, give it the “Dataflow Worker” role and don’t forget to export it as a JSON at the end so we can use it later.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cuDQ0Td55u2JLCtQwH8LYA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yOveV8vOrqMql5HAOPWamA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CTjfVEjGHO50efmkIKdX_A.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*px3D6Pi_OUOWYH8ckN2moA.png" /></figure><p>After downloading the service account JSON we need to set it up as an environment variable</p><pre>export GOOGLE_APPLICATION_CREDENTIALS=&quot;/Users/afragotsis/Downloads/my_project.json&quot;</pre><p>Assuming this is the JSON we want to parse is this format</p><pre>{<br>  &quot;product&quot;: {<br>    &quot;id&quot;: 1234567890,<br>    &quot;title&quot;: &quot;My Super Awesome Product&quot;,<br>    &quot;vendor&quot;: &quot;Vendor Test&quot;,<br>    &quot;product_type&quot;: &quot;Type Test&quot;,<br>    &quot;created_at&quot;: &quot;2020-05-11T16:07:45-04:00&quot;,<br>    &quot;updated_at&quot;: &quot;2020-05-26T14:32:09-04:00&quot;,<br>    &quot;options&quot;: [<br>      {<br>        &quot;id&quot;: 1234567890<br>      }<br>    ]<br>  }<br>}</pre><p>Now we’ll write the pipeline. Stage 1 will read the JSON file directly from the file in GCS bucket, we’re using smart_open for this.</p><pre>from google.cloud import storage<br>from smart_open import open<br><br>class ReadFile(beam.DoFn):<br><br>    def __init__(self, input_path):<br>        self.input_path = input_path<br><br>    def start_bundle(self):<br>        self.client = storage.Client()<br><br>    def process(self, something):<br>        clear_data = []<br>        with open(self.input_path) as fin:<br>            for line in fin:<br>                data = json.loads(line)<br>                product = data.get(&#39;product&#39;)<br><br>                if product and product.get(&#39;id&#39;):<br>                    product_id = str(product.get(&#39;id&#39;))<br>                    vendor = product.get(&#39;vendor&#39;)<br>                    product_type = product.get(&#39;product_type&#39;)<br>                    updated_at = product.get(&#39;updated_at&#39;)<br>                    created_at = product.get(&#39;created_at&#39;)<br>                    product_options = product.get(&#39;options&#39;)<br><br>                    option_ids = []<br>                    if product_options:<br>                        for option in product_options:<br>                            option_ids.append(option.get(&#39;id&#39;))<br><br>                    clear_data.append([product_id, vendor, product_type, updated_at, created_at, option_ids])<br><br>        yield clear_data</pre><p><strong>yield clear_data </strong>passes the array we created to the next stage of the pipeline.</p><p>Stage 2 will get as input the cleared data and write the CSV file to the output path using <strong>Pandas</strong></p><pre>import pandas as pd<br>from google.cloud import storage</pre><pre>class WriteCSVFIle(beam.DoFn):<br><br>    def __init__(self, bucket_name):<br>        self.bucket_name = bucket_name<br><br>    def start_bundle(self):<br>        self.client = storage.Client()<br><br>    def process(self, mylist):<br>        df = pd.DataFrame(mylist, columns={&#39;product_id&#39;: str, &#39;vendor&#39;: str, &#39;product_type&#39;: str, &#39;updated_at&#39;: str, &#39;created_at&#39;: str, &#39;option_ids&#39;: str})<br><br>        bucket = self.client.get_bucket(self.bucket_name)</pre><pre>        bucket<br>          .blob(f&quot;csv_exports.csv&quot;)<br>          .upload_from_string(df.to_csv(index=False), &#39;text/csv&#39;)</pre><p>Write the actual Dataflow pipeline and save the file as DataflowTest.py</p><pre>class DataflowOptions(PipelineOptions):<br><br>    @classmethod<br>    def _add_argparse_args(cls, parser):<br>        parser.add_argument(&#39;--input_path&#39;, type=str, default=&#39;gs://alex_dataflow_temp/input.json&#39;)<br>        parser.add_argument(&#39;--output_bucket&#39;, type=str, default=&#39;alex_dataflow_temp&#39;)<br><br><br>def run(argv=None):<br>    parser = argparse.ArgumentParser()<br>    known_args, pipeline_args = parser.parse_known_args(argv)<br><br>    pipeline_options = PipelineOptions(pipeline_args)<br>    dataflow_options = pipeline_options.view_as(DataflowOptions)<br><br>    with beam.Pipeline(options=pipeline_options) as pipeline:<br>        (pipeline<br>         | &#39;Start&#39; &gt;&gt; beam.Create([None])<br>         | &#39;Read JSON&#39; &gt;&gt; beam.ParDo(ReadFile(dataflow_options.input_path))<br>         | &#39;Write CSV&#39; &gt;&gt; beam.ParDo(WriteCSVFIle(dataflow_options.output_bucket))<br>         )<br><br><br>if __name__ == &#39;__main__&#39;:<br>    logging.getLogger().setLevel(logging.INFO)<br>    run()</pre><p>Finally, we need to create the requirements file with all the libraries we’ve used in our pipeline.</p><p><strong>requirements.txt</strong></p><pre>google-cloud-storage==1.28.1<br>pandas==1.0.3<br>smart-open==2.0.0</pre><p>Before generating the template file we need to create the temp and staging folders in our GCS bucket.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/444/1*oqCEXqI7Oed7OIAkXHfd8Q.png" /></figure><p>Now we can generate the Dataflow Template running the following command in the terminal from within the folder we have DataflowTest.py.</p><pre>python DataflowTest.py \<br>    --runner DataflowRunner \<br>    --project my_project \<br>    --staging_location gs://alex_dataflow_staging/ \<br>    --temp_location gs://alex_dataflow_temp/ \<br>    --template_location gs://alex_dataflow_template/MyTemplate \<br>    --requirements_file requirements.txt \<br>    --save_main_session True</pre><p>The staging and temp locations are going to be used from Dataflow, template location is the bucket where the template is going to be stored.</p><p>** If for any reason the above command doesn’t work we might need to change and instead of using the requirements file we need to use the setup file.</p><p><strong>setup.py</strong></p><pre>import setuptools<br><br>REQUIRED_PACKAGES = [<br>    &#39;google-cloud-storage==1.28.1&#39;,<br>    &#39;pandas==1.0.3&#39;,<br>    &#39;smart-open==2.0.0&#39;<br>]<br><br>PACKAGE_NAME = &#39;my_package&#39;<br>PACKAGE_VERSION = &#39;0.0.1&#39;<br><br>setuptools.setup(<br>    name=PACKAGE_NAME,<br>    version=PACKAGE_VERSION,<br>    description=&#39;My setup file&#39;,<br>    install_requires=REQUIRED_PACKAGES,<br>    packages=setuptools.find_packages(),<br>)</pre><p>and the dataflow template export command will look like this. Note that in the setup_file variable we need to put the <strong>full path</strong> on your local machine</p><pre>python DataflowTest.py \<br>    --runner DataflowRunner \<br>    --project my_project \<br>    --staging_location gs://alex_dataflow_staging/ \<br>    --temp_location gs://alex_dataflow_temp/ \<br>    --template_location gs://alex_dataflow_template/MyTemplate \<br><strong>    --setup_file /Users/afragotsis/Documents/github/.../setup.py \<br></strong>    --save_main_session True</pre><p>After running the job we can find the template file in our GCS bucket.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/920/1*WqgPwzOEolAuhkjItDVg8g.png" /></figure><p>Now we’re ready to run the Dataflow Job. Select “Dataflow” on the Google Console and then “Create Job from Template”, give the name “<strong>my_test_job</strong>” and then on the “Dataflow Template” select “<strong>Custom Template</strong>”. Browse and select the template on the path we’ve uploaded it, finally write the temp folder path uri.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GwkLmilhhIDHyd3VlpEZJQ.png" /></figure><p>after running we should see this in the dataflow execution console</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/235/1*xRy_ul8rYRjnSkG_aR8yuQ.png" /></figure><p>and finally the CSV file in the temp folder</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/532/1*uvnHHzP-ZNsgLnVGQlZTWQ.png" /></figure><p>see full code here <a href="https://github.com/aFrag/PythonDataflow/blob/master/JsonToCsv.py">https://github.com/aFrag/PythonDataflow/blob/master/JsonToCsv.py</a></p><p>After you created your templates see how you can schedule them to run automatically with Airflow <a href="https://medium.com/@alexanderfragotsis/schedule-dataflow-templates-with-airflow-1422ecfea677">here</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=92906c87cc96" width="1" height="1" alt=""><hr><p><a href="https://medium.com/analytics-vidhya/transform-json-to-csv-from-google-bucket-using-a-dataflow-python-pipeline-92906c87cc96">Transform JSON to CSV from Google bucket using a Dataflow Python pipeline</a> was originally published in <a href="https://medium.com/analytics-vidhya">Analytics Vidhya</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>