Oracle databases have been at the forefront of the data landscape for decades.
Organizations have come to depend heavily on them to increase operational efficiencies and execution of critical workloads.
As a database, Oracle has many advantages but it sometimes becomes essential to move data from Oracle to Snowflake. But before going into the details of how to do so, a short preview of the two is necessary.
Oracle Database
Oracle database facilitates quick and safe storage and retrieval of data and can run on varying hardware across operating systems. These include Windows Server, Unix, and GNU/Linux. Its networking stack feature allows seamless integration of applications to the Oracle database.
Oracle has always been committed to open technologies and is one of the first databases to have supported GNU/Linux a couple of decades ago even before it became a commerce product. Finally, Oracle is an ACID-compliant database, thereby ensuring that it maintains data integrity and reliability.
Snowflake
Snowflake is an analytic data warehouse and comes as a Software-as-a-Service (SaaS) product. When compared to traditional data warehouses, it is faster and more user-friendly and flexible.
The Snowflake architecture is exclusively designed for the cloud and is not built on any existing database or “big data” software. Instead, it uses a new SQL database engine with additional functionalities and distinctive capabilities.
Snowflake runs completely in public cloud infrastructure and hence users do not have to select, install configure, or manage hardware and software. All maintenance, management and tuning issues are handled directly by Snowflake.
Why Move Databases from Oracle to Snowflake
There is no doubt that Oracle has several advantages as a database. Then why should organizations need to move databases from Oracle to Snowflake?
- Snowflake Cloud Data warehouse solution offers unmatched agility and elasticity. Users can scale up and down whenever needed and pay only for resources used.
- Snowflake has a high computational ability. Even when several concurrent users are running intricate queries, the computational power of Snowflake ensures there is no drag or slow-down, thereby reducing wait times significantly.
- Snowflake enables users to work effortlessly with both structured and semi-structured data. This aspect is very difficult in Oracle.
- Snowflake is a comprehensively managed service and enterprises can quickly have their analytics and data processing projects up and running without investing in engineering resources.
In this post, there will be a step by step guide to moving databases from Oracle to Snowflake.
Moving from Oracle to Snowflake
There are several steps to be followed for moving databases from Oracle to Snowflake which will be analyzed here in some detail.
Step 1 – Mining Data from Oracle to CSV file
Every Oracle Database Server has an SQL Plus query tool installed which can be used to query and redirect the result of the query to a CSV file. The command that is used to execute this activity is “Spool”. The results are written in the file specified in the command until Spool is turned off.
An existing Spool file will be overwritten by default but if one is not there a new file will be created. Generally, data extraction logic will be carried out in the Shell script.
Any SQL can be run once Spool is on and the result will be reflected in the specified file. However, if data has to be extracted incrementally that is, only records that are changed after the last pull has to be selected, SQL has to be generated with proper conditions.
Step 2 – Converting and Formatting Data Type
While moving data from Oracle to Snowflake, it might be necessary to convert and format data as per the specific business needs of an organization. Apart from such particular instances, certain aspects have to be kept in mind to make sure that the transition is smooth and without any glitches.
- Snowflake does support all major character sets including ISO-8859-1 to 9, Big5, EUC-KR, UTF-8, UTF-16, and more. But it is critical to monitor that no errors are caused by character sets mismatch in source and target.
- Generally while moving data from Oracle to Big Data systems, data integrity is often compromised because of the paucity of support for SQL constraints. However, all SQL constraints like UNIQUE, PRIMARY KEY, FOREIGN KEY, NOT NULL are supported by Snowflake and this is a huge advantage for moving data on expected lines.
- With the “File Format Option” in Snowflake, a custom format can be created to insert dates or time to a file in the table.
- Snowflake covers most primitive and advanced data types that also include nested data structures.
Step 3 – Loading Data to Cloud Staging Area
Before loading data to Snowflake, it is essential to upload data first to a cloud staging area. For Snowflake running on AWS, the data has to be uploaded to a location that Snowflake has access to like S3. The Snowflake stage can be either external or internal.
In an external staging option, Snowflake supports any accessible Amazon S3 or Microsoft Azure as a staging location. A stage can be created pointing to the location data that can then be loaded directly to the Snowflake table though that stage. However, for creating an external stage pointing to an S3 location, IAM credentials with the required permissions are needed.
In the internal staging option, every user and table will be automatically allotted to an internal stage. It can be used to stage data to that user and table. A name can be assigned to each internal stage created. When data is loaded to a table through an internal stage, options like file format and date format are automatically applied.
Step 4 – Copying Staged Files to Snowflake Table
In the previous steps, it has been seen how data is extracted and uploaded from Oracle to Snowflake in an S3 location and how the Snowflake stage pointing to that location is created. The final step is to copy data to the table with the “COPY INTO” command. To do this Snowflake credits are utilized and compute resources in Snowflake virtual warehouses are required.
It has been seen how SQL constraints are supported by Snowflake. Another feature of Snowflake is that it supports row-level data manipulations which make it easy to handle delta data load.
The primary idea is to load incrementally extracted data into a transitional or temporary table from where records can be modified and transferred to the final table. Follow these simple steps for loading databases from Oracle to Snowflake.