<?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 nikkileenee on Medium]]></title>
        <description><![CDATA[Stories by nikkileenee on Medium]]></description>
        <link>https://medium.com/@jojolee_23991?source=rss-9ae956317c61------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*v1fP4g4AM-Kc620vT98oqg@2x.jpeg</url>
            <title>Stories by nikkileenee on Medium</title>
            <link>https://medium.com/@jojolee_23991?source=rss-9ae956317c61------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 13:30:04 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@jojolee_23991/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[AWS S3 Cross accounts replication]]></title>
            <link>https://medium.com/@jojolee_23991/aws-s3-cross-accounts-replication-46056056ae3b?source=rss-9ae956317c61------2</link>
            <guid isPermaLink="false">https://medium.com/p/46056056ae3b</guid>
            <category><![CDATA[aws-s3]]></category>
            <category><![CDATA[aws-cross-account]]></category>
            <category><![CDATA[s3]]></category>
            <dc:creator><![CDATA[nikkileenee]]></dc:creator>
            <pubDate>Tue, 23 May 2023 07:02:00 GMT</pubDate>
            <atom:updated>2023-05-23T07:02:00.193Z</atom:updated>
            <content:encoded><![CDATA[<p>There are two ways to copy objects cross accounts:</p><ul><li>Role level</li><li>Resource level</li></ul><h3><strong>Role level</strong></h3><p>We have source and destination accounts, each account has the following settings:</p><ul><li>Source Account A:</li></ul><p>— Bucket A</p><p>— KMS A encrypted on Bucket A</p><p>— Role A</p><ul><li>Destination Account B</li></ul><p>— Bucket B</p><p>— KMS B encrypted on Bucket B</p><p>— Role B</p><p>Here’s what we need to do in each accounts:</p><ul><li>In destination account B:</li></ul><p>— IAM role: assume Role A for Role B, and also assume it in trust relationship.</p><p>— KMS: allow A role to use KMS B</p><ul><li>In source account A:</li></ul><p>— In role A, allow <strong>stsAssume </strong>for Role B, and also assume it in <strong>trust relationship</strong></p><p>— Grant S3 level access to <strong>listBucket </strong>and <strong>GetObject </strong>for Bucket A</p><p>— In A role, allow “<strong>s3:ListBucket</strong>”, “<strong>s3:PutObject</strong>”, “<strong>s3:PutObjectAcl</strong>” to <strong>B bucket </strong>and <strong>B bucket/*</strong></p><p>— In A role, allow “<strong>kms:Encrypt</strong>”, “<strong>kms:Decrypt</strong>”, “<strong>kms:ReEncrypt</strong>*”, “<strong>kms:DescribeKey</strong>” and “<strong>kms:GenerateDataKey</strong>”on <strong>KMS A </strong>and <strong>KMS B</strong></p><pre>import json import boto3  <br>        <br>source_role_a = &#39;&lt;source_role_arn&gt;&#39;     <br>s3_bucket_a = &#39;&lt;s3_bucket_name_a&gt;&#39;     <br>prefix = &#39;&lt;file_prefix in s3_bucket_a&gt;&#39;          <br>s3_bucket_b = &#39;&lt;s3_bucket_name_b&gt;&#39;          <br>stsClient = boto3.client(&#39;sts&#39;, region_name=&#39;us-east-1&#39;, endpoint_url=&#39;https://sts.us-east-1.amazonaws.com&#39;)     <br>stsResponse = stsClient.assume_role(         <br>                RoleArn=&#39;&lt;source role arn&gt;&#39;,         <br>                RoleSessionName=&#39;role_sts_session&#39;<br>              )       <br>secretAccessId = stsResponse[&quot;Credentials&quot;][&quot;AccessKeyId&quot;]     <br>secretAccessKey = stsResponse[&quot;Credentials&quot;][&quot;SecretAccessKey&quot;]     <br>sessionToken = stsResponse[&quot;Credentials&quot;][&quot;SessionToken&quot;]  <br><br>### GetSessionToken cannot assume the role for more than an hour.    <br>s3_assume_client = boto3.client(         <br>                  &#39;s3&#39;,         <br>                  region_name=&#39;us-east-1&#39;,         <br>                  aws_access_key_id=secretAccessId,         <br>                  aws_secret_access_key=secretAccessKey,         <br>                  aws_session_token=sessionToken     <br>                )    <br> <br>objs = s3_assume_client.list_objects(Bucket=s3_bucket_a, Prefix=prefix)[&#39;Contents&#39;]     <br>print(&#39;objs&#39;)     <br>print(objs)          <br>print(f&quot;Copying from bucket {s3_bucket}&quot;)     <br>print(f&quot;Copying to bucket {target_bucket_name}&quot;)          <br>print(f&quot;Copying file {prefix}&quot;)      <br>copy_src = {&#39;Bucket&#39;: s3_bucket_a, &#39;Key&#39;: prefix}     <br>s3_assume_client.copy_object(CopySource=copy_src, Bucket=s3_bucket_b, Key=prefix, ServerSideEncryption=&#39;aws:kms&#39;, SSEKMSKeyId=&#39;&lt;KMS_b&gt;&#39;)     </pre><h3><strong>Resource level</strong></h3><p>We can create replication rules for source s3 bucket on using source or destination account.</p><p>The following access can allow destination account create replication rules on source s3 bucket.</p><p>Prerequisites:</p><ul><li>Enable Versioning on source and destination buckets</li></ul><p><em>Batch Operation is touching buckets other than source and destination bucket, so we need to allow s3 actions on resource “*”</em></p><h4><strong>Source account</strong></h4><p>Source team should have a replication role to create replication rule on s3 buckets, and a role allow destination team to sts assume.</p><p>Those two roles can be one role.</p><blockquote>IAM role configuration</blockquote><ul><li><strong>Replication role: </strong>policy</li></ul><pre>{<br> &quot;Version&quot;: &quot;2012-10-17&quot;,<br> &quot;Statement&quot;: [{<br>   &quot;Action&quot;: [<br>    &quot;s3:GetReplicationConfiguration&quot;,<br>    &quot;s3:ListBucket&quot;<br>   ],<br>   &quot;Resource&quot;: &quot;&lt;source team s3 bucket arn&gt;&quot;,<br>   &quot;Effect&quot;: &quot;Allow&quot;<br>  },<br>  {<br>   &quot;Action&quot;: [<br>    &quot;s3:GetObjectVersion&quot;,<br>    &quot;s3:GetObjectVersionAcl&quot;,<br>    &quot;s3:GetObjectVersionTagging&quot;,<br>    &quot;s3:GetObjectVersionForReplication&quot;<br>   ],<br>   &quot;Resource&quot;: &quot;&lt;source team s3 bucket arn&gt;/*&quot;,<br>   &quot;Effect&quot;: &quot;Allow&quot;<br>  },<br>  {<br>   &quot;Condition&quot;: {<br>    &quot;StringLike&quot;: {<br>     &quot;kms:ViaService&quot;: &quot;s3.us-east-1.amazonaws.com&quot;,<br>     &quot;kms:EncryptionContext:aws:s3:arn&quot;: [<br>      &quot;&lt;source team s3 bucket arn&gt;/*&quot;<br>     ]<br>    }<br>   },<br>   &quot;Action&quot;: [<br>    &quot;kms:Encrypt&quot;,<br>    &quot;kms:Decrypt&quot;,<br>    &quot;kms:ReEncrypt*&quot;,<br>    &quot;kms:GenerateDataKey*&quot;,<br>    &quot;kms:DescribeKey&quot;<br>   ],<br>   &quot;Resource&quot;: &quot;&lt;source team kms arn&gt;&quot;,<br>   &quot;Effect&quot;: &quot;Allow&quot;<br>  },<br>  {<br>   &quot;Condition&quot;: {<br>    &quot;StringLikeIfExists&quot;: {<br>     &quot;s3:x-amz-server-side-encryption&quot;: [&quot;aws:kms&quot;, &quot;AES256&quot;]<br>    }<br>   },<br>   &quot;Action&quot;: [&quot;s3:List*&quot;, &quot;s3:GetBucketVersioning&quot;, &quot;s3:PutBucketVersioning&quot;, &quot;s3:ReplicateObject&quot;, &quot;s3:ReplicateDelete&quot;, &quot;s3:ReplicateTags&quot;, &quot;s3:GetObjectVersionTagging&quot;, &quot;s3:ObjectOwnerOverrideToBucketOwner&quot;],<br>   &quot;Resource&quot;: [&quot;&lt;destinaton s3 bucket arn&gt;&quot;, &quot;&lt;destinaton s3 bucket arn&gt;/*&quot;],<br>   &quot;Effect&quot;: &quot;Allow&quot;<br>  }, {<br>   &quot;Condition&quot;: {<br>    &quot;StringLike&quot;: {<br>     &quot;kms:ViaService&quot;: &quot;s3.us-east-1.amazonaws.com&quot;,<br>     &quot;kms:EncryptionContext:aws:s3:arn&quot;: [&quot;&lt;destinaton s3 bucket arn&gt;&quot;, &quot;&lt;destinaton s3 bucket arn&gt;/*&quot;]<br>    }<br>   },<br>   &quot;Action&quot;: [&quot;kms:Encrypt&quot;, &quot;kms:Decrypt&quot;, &quot;kms:ReEncrypt*&quot;, &quot;kms:GenerateDataKey*&quot;, &quot;kms:DescribeKey&quot;],<br>   &quot;Resource&quot;: [&quot;&lt;destination team s3 kms arn&gt;&quot;],<br>   &quot;Effect&quot;: &quot;Allow&quot;<br>  }<br> ]<br>}</pre><ul><li>Trust Relationship</li></ul><pre>{<br> &quot;Version&quot;: &quot;2012-10-17&quot;,<br> &quot;Statement&quot;: [{<br>  &quot;Effect&quot;: &quot;Allow&quot;,<br>  &quot;Principal&quot;: {<br>   &quot;Service&quot;: &quot;s3.amazonaws.com&quot;<br>  },<br>  &quot;Action&quot;: &quot;sts:AssumeRole&quot;<br> }]<br>}</pre><ul><li><strong>Ats assume role: </strong>policy</li></ul><pre>{<br> &quot;Version&quot;: &quot;2012-10-17&quot;,<br> &quot;Statement&quot;: [{<br>  &quot;Action&quot;: [&quot;kms:Decrypt&quot;, &quot;kms:Encrypt&quot;, &quot;kms:DescribeKey&quot;, &quot;kms:ReEncrypt*&quot;, &quot;kms:GenerateDataKey*&quot;],<br>  &quot;Resource&quot;: [&quot;&lt;source bucket kms arn&gt;&quot;, &quot;&lt;destination bucket kms arn&gt;&quot;]<br>  &quot;Effect&quot;: &quot;Allow&quot;<br> }, {<br>  &quot;Action&quot;: [&quot;s3:PutObject&quot;, &quot;s3:PutObjectAcl&quot;, &quot;s3:PutObjectLegalHold&quot;, &quot;s3:PutObjectRetention&quot;, &quot;s3:PutObjectTagging&quot;, &quot;s3:PutObjectVersionTagging&quot;, &quot;s3:Abort*&quot;],<br>  &quot;Resource&quot;: [&quot;&lt;source bucket arn&gt;/*&quot;, &quot;&lt;destination bucket arn&gt;/*&quot;]<br>  &quot;Effect&quot;: &quot;Allow&quot;<br> }, {<br>  &quot;Action&quot;: [&quot;s3:GetObject*&quot;, &quot;s3:GetBucket*&quot;, &quot;s3:List*&quot;],<br>  &quot;Resource&quot;: [&quot;&lt;source bucket arn&gt;&quot;, &quot;&lt;source bucket arn&gt;/*&quot;, &quot;&lt;destination bucket arn&gt;&quot;, &quot;&lt;destination bucket arn&gt;/*&quot;],<br>  &quot;Effect&quot;: &quot;Allow&quot;<br> }]<br>}</pre><ul><li><strong>Ats assume role</strong>: put replication on source buckets</li></ul><pre>{<br> &quot;Version&quot;: &quot;2012-10-17&quot;,<br> &quot;Statement&quot;: [{<br>    &quot;Action&quot;: [&quot;s3:PutReplicationConfiguration&quot;, &quot;s3:GetReplicationConfiguration&quot;],<br>    &quot;Resource&quot;: &quot;&lt;source bucket arn&gt;&quot;,<br>    &quot;Effect&quot;: &quot;Allow&quot;<br>   }, {<br>    &quot;Action&quot;: &quot;iam:PassRole&quot;,<br>    &quot;Resource&quot;: &quot;&lt;replication role arn&gt;&quot;, ## if use different role to put replication<br>    &quot;Effect&quot;: &quot;Allow&quot;<br>   }</pre><ul><li>Trust relationship:</li></ul><pre>{<br> &quot;Version&quot;: &quot;2012-10-17&quot;,<br> &quot;Statement&quot;: [{<br>  &quot;Effect&quot;: &quot;Allow&quot;,<br>  &quot;Principal&quot;: {<br>   &quot;Service&quot;: &quot;&lt;destination service princial&gt;&quot;,<br>   &quot;AWS&quot;: &quot;&lt;destination role arn if there&#39;s any&gt;&quot;<br>  },<br>  &quot;Action&quot;: &quot;sts:AssumeRole&quot;<br> }]<br>}</pre><blockquote>KMS configuration</blockquote><p>For those roles, we also need to allow their access in bucket KMS ‘Key policy’</p><pre>{<br> &quot;Version&quot;: &quot;2012-10-17&quot;,<br> &quot;Statement&quot;: [{<br>  &quot;Effect&quot;: &quot;Allow&quot;,<br>  &quot;Principal&quot;: {<br>   &quot;AWS&quot;: &quot;arn:aws:iam::&lt;source account number&gt;:root&quot;<br>  },<br>  &quot;Action&quot;: [&quot;kms:Create*&quot;, &quot;kms:Describe*&quot;, &quot;kms:Enable*&quot;, &quot;kms:List*&quot;, &quot;kms:Put*&quot;, &quot;kms:Update*&quot;, &quot;kms:Revoke*&quot;, &quot;kms:Disable*&quot;, &quot;kms:Get*&quot;, &quot;kms:Delete*&quot;, &quot;kms:ScheduleKeyDeletion&quot;, &quot;kms:CancelKeyDeletion&quot;, &quot;kms:GenerateDataKey*&quot;, &quot;kms:Decrypt&quot;],<br>  &quot;Resource&quot;: &quot;*&quot;<br> }]<br>}</pre><blockquote>Bucket policy configuration</blockquote><pre>{<br> &quot;Effect&quot;: &quot;Allow&quot;,<br> &quot;Principal&quot;: {<br>  &quot;Service&quot;: &quot;&lt;internal service principal&gt;&quot;<br> },<br> &quot;Action&quot;: [&quot;s3:AbortMultipartUpload&quot;, &quot;s3:GetBucketLocation&quot;, &quot;s3:GetObject&quot;, &quot;s3:ListBucket&quot;, &quot;s3:ListBucketMultipartUploads&quot;, &quot;s3:PutObject&quot;, &quot;s3:PutObjectAcl&quot;],<br> &quot;Resource&quot;: [&quot;&lt;source bucket arn&gt;&quot;, &quot;&lt;source bucket arn&gt;/*&quot;]<br>}]<br>}</pre><h4>Destination account</h4><p>On destination side, we need to allow source role assume our role, and s3 permission in bucket policy.</p><blockquote>IAM role configuration</blockquote><p>Add sts assume permission in destination role trust relationship:</p><pre>{<br> &quot;Version&quot;: &quot;2012-10-17&quot;,<br> &quot;Statement&quot;: [{<br>  &quot;Effect&quot;: &quot;Allow&quot;,<br>  &quot;Principal&quot;: {<br>   &quot;AWS&quot;: &quot;&lt;source ats assume role arn&gt;&quot;<br>  },<br>  &quot;Action&quot;: &quot;sts:AssumeRole&quot;<br> }]<br>}</pre><p>Role policy:</p><pre>{<br> &quot;Version&quot;: &quot;2012-10-17&quot;,<br> &quot;Statement&quot;: [{<br>  &quot;Action&quot;: &quot;sts:AssumeRole&quot;,<br>  &quot;Resource&quot;: [&quot;&lt;source ats assume role arn&gt;&quot;],<br>  &quot;Effect&quot;: &quot;Allow&quot;,<br>  &quot;Sid&quot;: &quot;StsIAMRole&quot;<br> }]<br>}</pre><blockquote>KMS configuration</blockquote><p>Also make sure the role is allowed in KMS key policy.</p><blockquote>Bucket policy configuration</blockquote><pre>{<br> &quot;Version&quot;: &quot;2008-10-17&quot;,<br> &quot;Statement&quot;: [{<br>  &quot;Sid&quot;: &quot;DenyUnSecureCommunications&quot;,<br>  &quot;Effect&quot;: &quot;Deny&quot;,<br>  &quot;Principal&quot;: &quot;*&quot;,<br>  &quot;Action&quot;: &quot;s3:*&quot;,<br>  &quot;Resource&quot;: [&quot;&lt;destination bucket arn&gt;/*&quot;, &quot;&lt;destination bucket arn&gt;&quot;],<br>  &quot;Condition&quot;: {<br>   &quot;Bool&quot;: {<br>    &quot;aws:SecureTransport&quot;: &quot;false&quot;<br>   }<br>  }<br> }, {<br>  &quot;Sid&quot;: &quot;PreventBucketDelete&quot;,<br>  &quot;Effect&quot;: &quot;Deny&quot;,<br>  &quot;Principal&quot;: &quot;*&quot;,<br>  &quot;Action&quot;: &quot;s3:DeleteBucket&quot;,<br>  &quot;Resource&quot;: &quot;&lt;destination bucket arn&gt;&quot;<br> }, {<br>  &quot;Sid&quot;: &quot;Set permissions for objects&quot;,<br>  &quot;Effect&quot;: &quot;Allow&quot;,<br>  &quot;Principal&quot;: {<br>   &quot;AWS&quot;: &quot;&lt;source team replication role arn&gt;&quot;<br>  },<br>  &quot;Action&quot;: [&quot;s3:ReplicateObject&quot;, &quot;s3:ReplicateDelete&quot;],<br>  &quot;Resource&quot;: &quot;&lt;destination bucket arn&gt;/*&quot;<br> }, {<br>  &quot;Sid&quot;: &quot;Set permissions on bucket&quot;,<br>  &quot;Effect&quot;: &quot;Allow&quot;,<br>  &quot;Principal&quot;: {<br>   &quot;AWS&quot;: &quot;&lt;source team replication role arn&gt;&quot;<br>  },<br>  &quot;Action&quot;: [&quot;s3:List*&quot;, &quot;s3:GetBucketVersioning&quot;, &quot;s3:PutBucketVersioning&quot;],<br>  &quot;Resource&quot;: &quot;&lt;destination bucket arn&gt;&quot;<br> }]<br>}</pre><p>Test script:</p><pre>import json <br>import boto3 <br>import logging   <br><br>  <br>source_sts_role_arn = &#39;&lt;source_sts_role_arn&gt;&#39;     <br>source_bucket_name = &#39;&lt;source_bucket_name&gt;&#39;     <br>s3_json_bucket = &#39;&lt;s3_json_bucket&gt;&#39;     <br>s3_json_path = &#39;&lt;s3_json_path&gt;&#39;           <br>s3_client = boto3.client(&#39;s3&#39;)     <br>result = s3_client.get_object(Bucket=s3_json_bucket, Key=s3_json_path)      <br>replication_config = result[&quot;Body&quot;].read().decode()     <br>replication_config_dict = json.loads(replication_config)               <br>stsClient = boto3.client(&#39;sts&#39;, region_name=&#39;us-east-1&#39;, endpoint_url=&#39;https://sts.us-east-1.amazonaws.com&#39;)          <br>stsResponse = stsClient.assume_role(     <br>          RoleArn=source_sts_role_arn,     <br>          RoleSessionName=&#39;role_session_name&#39;      <br>          )               <br>secretAccessId = stsResponse[&quot;Credentials&quot;][&quot;AccessKeyId&quot;]     <br>secretAccessKey = stsResponse[&quot;Credentials&quot;][&quot;SecretAccessKey&quot;]     <br>sessionToken = stsResponse[&quot;Credentials&quot;][&quot;SessionToken&quot;]          <br>s3_assume_client = boto3.client(     <br>              &#39;s3&#39;,     <br>              region_name=&#39;us-east-1&#39;,     <br>              aws_access_key_id=secretAccessId,     <br>              aws_secret_access_key=secretAccessKey,     <br>              aws_session_token=sessionToken     <br>            )     <br>response = s3_assume_client.put_bucket_replication(     <br>              Bucket=source_bucket_name,     <br>              ReplicationConfiguration=replication_config_dict     <br>            )     <br>print(&#39;response&#39;)     <br>print(response) </pre><p>Replication rule example:</p><pre>{<br> &#39;Role&#39;: &#39;&lt;source replication role arn&gt;&#39;,<br> &#39;Rules&#39;: [{<br>  &#39;ID&#39;: &#39;&lt;id&gt;&#39;,<br>  &#39;Priority&#39;: 1,<br>  &#39;Status&#39;: &#39;Enabled&#39;,<br>  &#39;DeleteMarkerReplication&#39;: {<br>   &#39;Status&#39;: &#39;Disabled&#39;<br>  },<br>  &#39;SourceSelectionCriteria&#39;: {<br>   &#39;SseKmsEncryptedObjects&#39;: {<br>    &#39;Status&#39;: &#39;Enabled&#39;<br>   }<br>  },<br>  &#39;Filter&#39;: {<br>   &#39;Prefix&#39;: &#39;&#39;<br>  },<br>  &#39;Destination&#39;: {<br>   &#39;Bucket&#39;: &#39;&lt;destination bucket arn&gt;&#39;,<br>   &#39;Account&#39;: &#39;&lt;destination account number&gt;&#39;,<br>   &#39;AccessControlTranslation&#39;: {<br>    &#39;Owner&#39;: &#39;Destination&#39;<br>   },<br>   &#39;EncryptionConfiguration&#39;: {<br>    &#39;ReplicaKmsKeyID&#39;: &#39;&lt;destination kms key&gt;&#39;<br>   }<br>  }<br> }]<br>}</pre><p>Test using AWS CLI:</p><pre># login to aws account  <br>aws sts assume-role --role-arn &lt;sts assume role&gt; --role-session-name whatever <br># export credentials <br># have configuration file ready  <br>cat rep-config.json  <br>aws s3api put-bucket-replication --bucket &lt;destination bucket name&gt;  --replication-configuration file://rep-config.json</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=46056056ae3b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Parse nested json objects with changing integer keys in Redshift]]></title>
            <link>https://medium.com/@jojolee_23991/parse-nested-json-with-different-keys-in-redshift-c4b3c3be912b?source=rss-9ae956317c61------2</link>
            <guid isPermaLink="false">https://medium.com/p/c4b3c3be912b</guid>
            <category><![CDATA[redshift-spectrum]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[redshift]]></category>
            <dc:creator><![CDATA[nikkileenee]]></dc:creator>
            <pubDate>Thu, 08 Sep 2022 04:59:41 GMT</pubDate>
            <atom:updated>2022-09-09T20:29:01.376Z</atom:updated>
            <content:encoded><![CDATA[<p>My sample table looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rCgThNwFIJHoM3mS6HUzMg.png" /></figure><p>The data type of ‘choices’ is struct.</p><p>Keys on choices don’t have a pattern and may vary. I want to parse keys and get values of ‘Display’ and map back to question_id.</p><p>The general idea is:</p><ul><li>get the max and min key values from choices</li><li>create a range table based on min and max</li><li>use <a href="https://docs.aws.amazon.com/redshift/latest/dg/JSON_EXTRACT_PATH_TEXT.html">json_extract_path_text</a> function to extract keys and ‘Display’ value</li></ul><ol><li>Since Redshift doesn’t support generate_series(), so just create a stored procedure similar to this:</li></ol><pre>CREATE OR REPLACE PROCEDURE TEST_GENERATE_SERIES_NUMBERS(START_NUMBER INT, END_NUMBER INT, STEP INT)<br>AS $$<br>BEGIN<br> DROP TABLE IF EXISTS choices;<br> CREATE TEMP TABLE choices (<br>    choice_number int<br> );<br> INSERT INTO choices<br> WITH RECURSIVE NUMBERS(N) as<br> (<br> select START_NUMBER<br> UNION ALL<br> select N + STEP from NUMBERS<br> where N &lt;= END_NUMBER and N + STEP &lt;= END_NUMBER<br> )<br> SELECT * FROM NUMBERS;<br>END;<br>$$<br>LANGUAGE plpgsql<br>;</pre><p>2. Create function that can find max and min key values. Here I use `plpythonu` to import json library.</p><pre>create or replace function choices_max_num (a varchar(65535))<br> returns varchar(65535)<br> stable<br>as $$<br> import json<br> a_str = “,”.join(json.loads(a).keys())<br> return max(a_str.split(‘,’))<br>$$ language plpythonu;</pre><pre>create or replace function choices_min_num (a varchar(65535))<br> returns varchar(65535)<br> stable<br>as $$<br> import json<br> a_str = “,”.join(json.loads(a).keys())<br> return min(a_str.split(‘,’))<br>$$ language plpythonu;</pre><p>3. A stored procedure to create a table that contain choices number range.</p><pre>CREATE OR REPLACE PROCEDURE TEST_GET_NUM_RANGE(table_name text)<br>AS $$<br>DECLARE<br> min_val int; max_val int;<br>BEGIN<br> drop table if exists get_choices_numbers;<br> execute ‘create temp table get_choices_numbers<br> as ( select choices, <br> choices_max_num(choices)::int as choices_max, <br> choices_min_num(choices)::int as choices_min<br> from ‘ || table_name ||’);’;<br> select max(choices_max) into max_val from get_choices_numbers;<br> select min(choices_min) into min_val from get_choices_numbers;<br> RAISE INFO ‘min_val = % max_val = %’, min_val, max_val;<br> CALL TEST_GENERATE_SERIES_NUMBERS(min_val,max_val,1);<br>END;<br>$$ LANGUAGE plpgsql;</pre><p>4. Get mapping use json_extract_path_text</p><pre>select quetion_id,<br>       cn.choice_number,<br>       json_extract_path_text(choices, cn.choice_number, &#39;Display&#39;) as choice_value<br>from choices_table, choices cn<br>where choice_value &lt;&gt; &#39;&#39;</pre><p>Finally, it will show the clean data:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OlfcYzZVAq5H9UX_BuSpBQ.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c4b3c3be912b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Query data from Cross Region Cross Account AWS Glue Data catalog]]></title>
            <link>https://medium.com/@jojolee_23991/query-data-from-cross-region-cross-account-aws-glue-data-catalog-2440a510b4c8?source=rss-9ae956317c61------2</link>
            <guid isPermaLink="false">https://medium.com/p/2440a510b4c8</guid>
            <category><![CDATA[glue-etl]]></category>
            <category><![CDATA[aws-glue]]></category>
            <dc:creator><![CDATA[nikkileenee]]></dc:creator>
            <pubDate>Sun, 20 Feb 2022 23:57:57 GMT</pubDate>
            <atom:updated>2022-02-20T23:57:57.385Z</atom:updated>
            <content:encoded><![CDATA[<p>I was working on a project where source glue data catalog is in us-west-2 but we needed to query it from us-east-1 region. There are lots of instruction on cross account, but not too many cases on cross region and cross account.</p><p>Steps are in the following:</p><ol><li>You account or etl role would need permissions on source S3 bucket itself on source account, specially List and Get.</li></ol><pre>{<br> &quot;Version&quot;: &quot;2008-10-17&quot;,<br> &quot;Statement&quot;: [<br>    {<br>     &quot;Sid&quot;: &quot;Access&quot;,<br>     &quot;Effect&quot;: &quot;Allow&quot;,<br>     &quot;Principal&quot;: {<br>        &quot;AWS&quot;: &quot;arn:aws:iam::&lt;you account id&gt;:root&quot;<br>      },<br>     &quot;Action&quot;: [<br>        &quot;s3:List*<em>&quot;,</em><br>        <em>&quot;</em>s3:Get*&quot;<br>      ],<br>     &quot;Resource&quot;: [<br>         &quot;arn:aws:s3:::&lt;source bucket in us-west-2&gt;&quot;,<br>         &quot;arn:aws:s3:::&lt;source bucket in us-west-2&gt;/*&quot;<br>      ]<br>    }<br>  ]<br>}</pre><p>2. Your account or etl role would need permission on source S3 bucket KMS Key</p><pre>{<br> &quot;Version&quot;: &quot;2012-10-17&quot;,<br> &quot;Id&quot;: &quot;key-consolepolicy&quot;,<br> &quot;Statement&quot;: [{<br>   &quot;Sid&quot;: &quot;Enable IAM User Permissions&quot;,<br>   &quot;Effect&quot;: &quot;Allow&quot;,<br>   &quot;Principal&quot;: {<br>    &quot;AWS&quot;: &quot;arn:aws:iam::&lt;source account id&gt;:root&quot;<br>   },<br>   &quot;Action&quot;: &quot;kms:*&quot;,<br>   &quot;Resource&quot;: &quot;*&quot;<br>  },<br>  {<br>   &quot;Sid&quot;: &quot;Allow access for Key Administrators&quot;,<br>   &quot;Effect&quot;: &quot;Allow&quot;,<br>   &quot;Principal&quot;: {<br>    &quot;AWS&quot;: &quot;arn:aws:iam::&lt;source account id&gt;:role/Admin&quot;<br>   },<br>   &quot;Action&quot;: [<br>    &quot;kms:Create*&quot;,<br>    &quot;kms:Describe*&quot;,<br>    &quot;kms:Enable*&quot;,<br>    &quot;kms:List*&quot;,<br>    &quot;kms:Put*&quot;,<br>    &quot;kms:Update*&quot;,<br>    &quot;kms:Revoke*&quot;,<br>    &quot;kms:Disable*&quot;,<br>    &quot;kms:Get*&quot;,<br>    &quot;kms:Delete*&quot;,<br>    &quot;kms:TagResource*&quot;,<br>    &quot;kms:UntagResource*&quot;,<br>    &quot;kms:ScheduleKeyDeletion*&quot;,<br>    &quot;kms:CancelKeyDeletion*&quot;<br>   ],<br>   &quot;Resource&quot;: &quot;*&quot;<br>  },<br>  {<br>   &quot;Sid&quot;: &quot;Allow use of the key&quot;,<br>   &quot;Effect&quot;: &quot;Allow&quot;,<br>   &quot;Principal&quot;: {<br>    &quot;AWS&quot;: [<br>     &quot;arn:aws:iam::&lt;source account id&gt;:role/Admin&quot;,<br>     &quot;arn:aws:iam::&lt;your account id&gt;:root&quot;<br>    ]<br>   },<br>   &quot;Action&quot;: [<br>    &quot;kms:Encrypt&quot;,<br>    &quot;kms:Decrypt&quot;,<br>    &quot;kms:ReEncrypt&quot;,<br>    &quot;kms:GenerateDataKey&quot;,<br>    &quot;kms:DescribeKey&quot;<br>   ],<br>   &quot;Resource&quot;: &quot;*&quot;<br>  },<br>  {<br>   &quot;Sid&quot;: &quot;Allow attachment of persistent resources&quot;,<br>   &quot;Effect&quot;: &quot;Allow&quot;,<br>   &quot;Principal&quot;: {<br>    &quot;AWS&quot;: &quot;arn:aws:iam::&lt;source account id&gt;:role/Admin&quot;<br>   },<br>   &quot;Action&quot;: [<br>    &quot;kms:CreateGrant&quot;,<br>    &quot;kms:ListGrants&quot;,<br>    &quot;kms:RevokeGrant&quot;<br>   ],<br>   &quot;Resource&quot;: &quot;*&quot;,<br>   &quot;Condition&quot;: {<br>    &quot;Bool&quot;: {<br>     &quot;kms:GrantIsForAWSResource&quot;: &quot;true&quot;<br>    }<br>   }<br>  }<br> ]<br>}</pre><p>3. You then make a call to glue_table api in us-west-2 to get the glue table definition and create the same table in your region which in this case is us-east-1.</p><p><a href="https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/glue.html#Glue.Client.create_table">Glue - Boto3 Docs 1.21.3 documentation</a></p><p>4. You might have to add partitions if there are new, by making a call to get_partitions</p><p><a href="https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/glue.html#Glue.Client.get_partitions">Glue - Boto3 Docs 1.21.3 documentation</a></p><p>5. You then create a glue job in your destination region and provide your destination bucket</p><pre>import sys<br>from awsglue.transforms import *<br>from awsglue.utils import getResolvedOptions<br>from pyspark.context import SparkContext<br>from awsglue.context import GlueContext<br>from awsglue.job import Job</pre><pre>## @params: [JOB_NAME]<br>args = getResolvedOptions(sys.argv, [&#39;JOB_NAME&#39;])</pre><pre>sc = SparkContext()<br>glueContext = GlueContext(sc)<br>spark = glueContext.spark_session<br>job = Job(glueContext)<br>job.init(args[&#39;JOB_NAME&#39;], args)</pre><pre>output_dir = &quot;s3://your-bucket-name/you-path/&quot;<br>items = glueContext.create_dynamic_frame.from_catalog(database=&lt;source db&gt;,table_name=&lt;source table name&gt;)<br>glueContext.write_dynamic_frame.from_options(frame = items, connection_type = &quot;s3&quot;, connection_options = {&quot;path&quot;: output_dir}, format = &lt;format you want&gt;)<br>job.commit()</pre><p>6. You can then compact the data based on the source partitions, run crawler and then create external schema in spectrum.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2440a510b4c8" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>