Hello, and welcome to the October update of our phData Toolkit series! Halloween is right around the corner, and we’re excited to see what costumes our friends, families, and trick-or-treaters come up with!
Our dedicated team has been hard at work to enhance your data management and integration experience. In this update, we’ve addressed various issues, introduced new features, and made improvements across different components.
Let’s dive into what’s new!
SQL Translation Updates
Are you tired of spending endless hours manually converting SQL queries between different dialects? Do you want to ensure accuracy and save valuable time while working with complex translations?
Look no further! Our SQL translation application is your answer. This revolutionary tool acts like a “Google Translate” for SQL dialects, making it a game-changer for database professionals and learners alike.
Over the course of the last month, we’ve made a number of updates to the platform to provide enhancements and fixes to a variety of translation pairings.
Translating From MSSQL to Snowflake
We’ve resolved the issue with the
INTERSECToperator when used inside an
Parsing problems with
GEOGRAPHYstatic methods are now fixed.
CREATE TABLE table1( col1 decimal(9, 6) NULL, col2 decimal(9, 6) NULL, col3 AS (geography::STPointFromText(((('POINT('+CONVERT(varchar(20),col2,(0)))+' ')+CONVERT(varchar(20),col1,(0)))+')',(4326))) );
CREATE TABLE table1 ( col1 DECIMAL(9, 6), col2 DECIMAL(9, 6), -- ERROR: line 4, character 10: Function return value incompatibility: Computed column for GEOGRAPHY data type. col3 DECIMAL(9, 6) AS TO_GEOGRAPHY( ( ( ( 'POINT(' || -- WARNING: line 4, character 49: Function argument incompatibility: STYLE argument for CONVERT function. LEFT(CAST(col2 AS VARCHAR), 20) ) || ' ' ) || -- WARNING: line 4, character 85: Function argument incompatibility: STYLE argument for CONVERT function. LEFT(CAST(col1 AS VARCHAR), 20) ) || ')' ) );
Translating From Oracle to Snowflake
You can now translate
CURSORstatements in Snowflake scripting.
We’ve addressed parsing issues within the
CREATE TABLEstatement for external table loading.
Our tool now handles
ALTER MATERIALIZED VIEWand
DROP MATERIALIZED VIEWstatements.
Parsing issues related to function column types in the
CREATE TABLEstatement have been resolved.
You’ll no longer encounter issues with the
Parsing problems with the
DATABASE LINKclause in
SELECT TABLEstatements are history.
We’ve fixed parsing issues with nested joins.
FORALLstatement can now be translated accurately, and variable declarations in Snowflake Scripting are handled correctly.
Parsing problems with
CREATE TABLEstatements are resolved.
CREATE TABLE t2 ( c1 VARCHAR2(255 CHAR) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY "d1" ACCESS PARAMETERS ( RECORDS DELIMITED BY 0x'0A' ) LOCATION ( 'fil1.txt' ) );
SELECT -- ERROR: line 5, character 1: Feature not supported: EXTERNAL TABLE LOADING. CREATE TABLE t2 (c1 VARCHAR(255));
Translating From Teradata to Snowflake
Our tool can now translate the
INTERVALdata types are now supported for smoother translations.
In summary, these updates to our SQL Translation tool offer enhanced accuracy, efficiency, and versatility when converting SQL queries between different dialects. By addressing specific issues and introducing new features, we’ve streamlined the process, reduced the risk of errors, and added valuable functionality.
This ultimately saves you time, minimizes the potential for translation mishaps, and empowers you to handle complex SQL translations with ease.
Toolkit CLI Updates
In our ongoing commitment to enhancing the functionality and reliability of our toolkit, we’re thrilled to introduce a host of exciting updates. We’ve implemented improved error handling for post-trigger provisioning operations, ensuring smoother and more error-resistant processes.
Additionally, we’ve added support for new password policy properties,
PASSWORD_HISTORY, giving you more control over your security policies.
For those who enjoy a seamless user experience, we’ve included a confirmation prompt when running
toolkit provision init with the
--metadata flag in an interactive shell, providing you with additional initialization details.
To streamline your operations, we’ve made an important change in the default ownership grant behavior. It’s now set to
COPY CURRENT GRANTS, which is a more reliable default option, especially when compared to the previous settings.
Our commitment to robustness continues with the fix for reader account provisioning, ensuring the successful migration of reader account metadata schemas in plan mode. We’ve also addressed the issues that occasionally arose with the new style account locator URL, making reader account provisioning a smoother process.
In response to user feedback, we’ve updated resource parameter delta calculations to consider the parameter level as well as its value, enhancing precision and customization.
Additionally, for those who need to execute SQL scripts as part of provisioning, we’ve introduced
postSql settings, allowing you to define paths to directories containing SQL files.
preSql runs prior to provisioning, and
postSql executes after successful provisioning, providing you with more flexibility in your workflow.
Lastly, for our Business Critical Edition Snowflake account users, we’ve added support for the
share_restrictions parameter on Shares. This feature allows you to share data from a Business Critical account to a non-business-critical account. For more information on this, check out the documentation.
The Journey Continues
Our recent round of updates and enhancements to our toolkit reflects our unwavering commitment to providing you with a top-notch, user-friendly experience. We’ve focused on refining the key aspects of error handling, security, and customization to ensure that your operations are not only smoother but also more secure and versatile.
From improved error handling for post-trigger provisioning operations to the addition of new password policy properties and the introduction of confirmation prompts, these updates aim to streamline your workflow and enhance the reliability of your processes.
We’ve also been attentive to user feedback, resulting in changes such as the default ownership grant behavior and fixes for reader account provisioning. Our updates cater to both precision and flexibility by refining resource parameter delta calculations and introducing preSql and postSql settings for executing SQL scripts.
These improvements are the result of our continuous efforts to deliver a toolkit that empowers you to work more efficiently and with fewer errors. Your feedback has been invaluable in guiding our development, and we look forward to further refining our toolkit to meet your evolving needs.
Thank you for choosing our toolkit, and we’re excited to continue working together towards even greater success in your data endeavors.
If you haven’t already explored the phData Toolkit, we highly recommend checking it out!
We encourage you to spend a few minutes browsing the apps and tools available in the phData Toolkit today to set yourself up for success in 2023.
Be sure to follow this series for more updates on the phData Toolkit tools and features.