The latest articles on DEV Community by Santhoshkumar. P (@sann3). https://dev.to/sann3 https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F304924%2F867f1d04-167f-4e9e-9c94-e94bd405d79c.jpg https://dev.to/sann3 en Santhoshkumar. P Fri, 23 Apr 2021 14:13:24 +0000 https://dev.to/sann3/bigquery-dynamic-sql-and-managing-temp-tables-5c43 https://dev.to/sann3/bigquery-dynamic-sql-and-managing-temp-tables-5c43 <p>Google introduced support for dynamic SQL in BigQuery. Developers working particularly in Oracle must have some liking for EXECUTE IMMEDIATE, the way you execute dynamic SQL queries. Such a feature in BigQuery was missing for a long time, and now that it is here, I can't wait to use it.</p> <h3> Choosing a problem statement </h3> <p>Let's choose a problem that easily resonates with every developer working with the Google BigQuery world. Who isn't noticing the large volume of temporary tables churned by the client drivers and large datasets. This is particularly true where downstream products implement a version of BigQuery driver and fail to leverage nice features like auto expiration of tables. Not so good part is the hygiene of the dataset, these tables stay forever until explicitly cleared. What is important for this blog is a problem statement to demonstrate the utility of dynamic SQL.</p> <h3> Lets address it using Dynamic SQL </h3> <p>Temporary tables do offer the convenience of caching large result sets. With data rapidly changing on BigQuery dataset, let us target the old temporary tables and remove those from the datasets. </p> <ul> <li>Our primary goal is to clear all temporary tables older than 24 hours. </li> <li>Achieving this goal needs some more information. We need to identify when a table was created. This is when INFORMATION_SCHEMA of BigQuery is helpful. </li> <li>Last step is that I want this to be scheduled every day, without my intervention. Yes, you can schedule SQL statements using the BigQuery scheduled query feature. </li> </ul> <p>To clear temporary tables across all datasets, let's write code employing dynamic SQL, iterate all the dataset using the INFORMATION_SCHEMA and delete the temp table using the timestamp and the name starting with temp_table_. And schedule the SQL code using the BigQuery scheduled query option. With this, all the temp tables that are older than 1 day should get automatically cleared at a daily cadence.</p> <div class="ltag_gist-liquid-tag"> </div> Santhoshkumar. P Wed, 01 Jan 2020 02:43:53 +0000 https://dev.to/sann3/data-platform-zone-names-a8e https://dev.to/sann3/data-platform-zone-names-a8e <p>I was in search of suitable names for zones in a data platform, and this is what I have until now.</p> <ul> <li>Access zone</li> <li>Additional zone</li> <li>Analytics zone</li> <li>Archive data zone</li> <li>Canonical data zone</li> <li>Certified zone</li> <li>Clean zone</li> <li>Cleansing zone</li> <li>Consumer zone</li> <li>Consumption zone</li> <li>Curated zone</li> <li>Dev zone</li> <li>Exploration zone</li> <li>Gold zone</li> <li>Insights zone</li> <li>Landing zone</li> <li>Master data zone</li> <li>Operationalization zone</li> <li>Persisted zone</li> <li>Process zone</li> <li>Production zone</li> <li>Published zone</li> <li>Raw zone</li> <li>Refined zone</li> <li>Refinery zone</li> <li>Reporting zone</li> <li>Sandbox zone</li> <li>Sensitive zone</li> <li>Silver zone</li> <li>Staging zone</li> <li>Standard zone</li> <li>Structured zone</li> <li>Temporal Zone</li> <li>Transformed zone</li> <li>Transient zone</li> <li>Trusted zone</li> <li>User Drop zone</li> <li>Work zone</li> </ul> <p><strong>Credits:</strong><br> Public blogs and images. </p> dataplatform zone data platform