My wife and I are foodies, and in addition to cooking and dining out, we enjoy watching some of the cooking competitions on TV. One of our favorite themes is when the celebrities are challenged to produce multiple dishes with the same ingredients.
Recently, our SharePlex customers and potential customers have challenged us with the question, “How do I get data out of my Oracle database into a file in AWS, or S3 storage”. Like great chefs, our SharePlex developers have given us multiple ways to answer that question. In this blog, I’ll give you a high-level overview of the three ways you can use SharePlex to help you move your valuable data from an Oracle database into AWS.
How AWS stores data
In addition to relational and “NoSQL” databases, AWS has other options for storing data. The two most common are S3 and “traditional” files.
S3 stores objects in buckets. Each bucket is typically accessed through a URL, for example, https://example-bucket.s3.amazonws.com/photos/mykids.jpg. Amazon provides APIs and Command Line interfaces to allow access to these objects.
Files are another way to store data in AWS. Files are collections of data stored on some sort of persistent media, usually hard disks or solid-state drives, created and accessed through an operating system such as Windows or Linux. Files can contain plain text, formatted documents or binary data such as a picture. An example of a file might be C:\My Pictures\Bobs 1st Birthday.jpg.
What’s so special about files?
Let’s look at why a customer might want their data in a “plain old file” in the first place. The biggest reason we see is to move data between systems. Most systems have at least one way to ingest or receive data in a file. In fact, for some systems, this may be the preferred way to ingest data. Also, data in files is portable, as we’ll see, files can be easily transferred between systems. In Amazon, files can be stored directly as files or they can be placed in an S3 bucket.
SharePlex and files
The SharePlex FILE target is perhaps one of the most versatile targets to which SharePlex can move data. The FILE target is a “change record” of exactly what happened in the database, an insert, update or delete. The file can also contain metadata about the database object being changed, including a definition of the schema, the date and time of the change, the operation that was performed and even which database user performed the change. Files can be in one of three formats, XML, JSON or SQL. XML and JSON are “portable” file formats that can contain information about the data, such as table and column names. The SQL file type is a record of the database changes in a “portable” subset of SQL (Structured Query Language). There are small examples of each of these formats at the end of this blog.
See the recipes in my next blog