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
Fixes:
We’ve resolved the issue with the
INTERSECToperator when used inside anEXISTSclause.Parsing problems with
GEOGRAPHYstatic methods are now fixed.
Example:
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
New Features:
You can now translate
CURSORstatements in Snowflake scripting.
Fixes:
We’ve addressed parsing issues within the
CREATE TABLEstatement for external table loading.Our tool now handles
ALTER MATERIALIZED VIEWandDROP MATERIALIZED VIEWstatements.Parsing issues related to function column types in the
CREATE TABLEstatement have been resolved.You’ll no longer encounter issues with the
OPAQUEclause inCREATE TABLEstatements.Parsing problems with the
DATABASE LINKclause inSELECT TABLEstatements are history.We’ve fixed parsing issues with nested joins.
The
FORALLstatement can now be translated accurately, and variable declarations in Snowflake Scripting are handled correctly.Parsing problems with
PARTITIONinCREATE TABLEstatements are resolved.
Example
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
New Features:
Our tool can now translate the
GRANTstatement.The
COALESCEfunction andINTERVALdata 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_MIN_AGE_DAYS, and 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 preSql and 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.
phData Toolkit
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.
