Loading Data into your Oracle (Cloud) Database

The database tools team provides applications and interfaces such as SQL Developer, SQL Developer Web, SQLcl, and Oracle REST Data Services.

There's a wide variety of ways these tools can be used to load data into your Oracle databases, especially if those databases are running in the Oracle Cloud.

This deck covers all of those options.

Transcript

1. Database Tool Overview Loading Data to the Oracle Cloud
2. Copyright © 2020, Oracle and/or its affiliates 2 Oracle Database Cloud Services • Whether you choose Oracle Databases on VMs, bare metal, or Exadata, Oracle Cloud provides the best infrastructure for running your database workload. • Utilize features found only in the Oracle Cloud such as RAC for High Availability, Active Data Guard for Disaster Recovery, and In-Memory Database for hyper fast analytics and reporting. 2 Virtual Machine Database Cloud Service Bare Metal Database Cloud Service Exadata Cloud Service
3. Automated Data Center Operations and Machine Learning Complete Infrastructure Automation Complete Database Automation Oracle Cloud Autonomous Database Automates Entire Database Stack Oracle Autonomous Database
4. Manage Automates all infrastructure and database maintenance Secure Protects data from all external and internal threats Provision Rapidly and easily creates mission critical databases Oracle Autonomous Database – What and How Protect Recovers from any failure without downtime Scale Scales online for highest performance and lowest cost 1 2 3 4 5 Optimize Optimally runs workloads without human direction
5. Compute Storage Networking/ Load Balancing Autonomous Database 2 x Databases 20 GB each 2 x VMs 1 GB Memory each 100 GB Block 10 GB Object 10 TB Outbound Data Transfer Available to All New and Existing Cloud Accounts Always Free – oracle.com/cloud/free
6. Autonomous Database Empowers Developers App Dev • Developers instantly create and effortlessly use autonomous databases • Eliminates dependence and delays on others • Eliminates database tuning • Integrated capabilities accelerate productivity • Advanced SQL, PL/SQL, JSON, Spatial, Analytic, IoT, and ML • The world’s best database is now the simplest to use • Dramatically easier and more productive than: Aurora, Azure SQL, DynamoDB, MongoDB Atlas, etc.
7. • Ability to REST enable a schema and autogenerate REST endpoints for tables, views, and procedures • Execute SQL and PL/SQL • Build Data Models, generate DDL statements • Monitor and manage the DB • Web-based Function rich, low code development env • No client software needed APEX Oracle REST Data Services SQL Developer Web Autonomous as a Development Environment Developer Tools out of the box with Autonomous
8. 8 Quarterly release schedules allow for frequent updates for both Cloud and On-Premises Experienced and flexible developers – can come in and out of projects and products as needed Powered by Java, Commons library makes connections, parser, formatters, etc. available to all products DBTools 2006 SQL Developer 2009 Modeler 2011 REST Data Services 2016 SQLcl 2018 SQL Developer Web
9. SQL Developer Web • Your favorite tool, in the browser • Load data - excel, csv, avro, xml… • Run SQL statements & scripts • Monitor and manage your DB • Schema diagramming • REST development interface • JSON Document Store (SODA) https://www.oracle.com/rest
10. • DBMS_CLOUD Scripts • Relational data to new or existing tables • Files to JSON Collections Data Loading with SQL Developer Web
11. Executing Scripts • SQL Worksheet supports all SQL and PL/SQL • Leverage DBMS_CLOUD to load data from Oracle Object Store • Many file formats supported Step-by-step Example “Use any AWS S3 compatible object store with Autonomous Database“
12. Importing Local Files • CSV, Delimited, Excel, JSON, & AVRO Files • Import Wizard • Mapped to relational table columns Step-by-step Example “Oracle SQL Developer Web: Importing JSON as Rows in a Relational Table”
13. Loading JSON Documents • JSON Document Store • One or more files to a collection • Point, click…query and go! Scheduled for version 20.4
14. • Run SQL statements • Load data • Database development • Monitor and manage • Data modeling – ERD • REST services development • Migrate 3rd party DMBS to Oracle SQL Developer https://www.oracle.com/sqldeveloper Used by over 5 million database pros
15. • Local delimited, CSV, Excel to new or existing tables • Object Store (CSV or Data Pump Imports) • Database Copy Wizard or Drag and Drop • Migrations (Redshift, SQL Server, DB2, …) Data Loading with SQL Developer
16. Local Files • Wizard for loading data to an existing or new table • Cart allows you to specify files for data loads (ad hoc / automation) • CSV, Excel, or Delimited Text files Step-by-step Example “How to Import from Excel to Oracle with SQL Developer“
17. Files in the Oracle Object Store (OSS) • Import Wizard, 1 file • Cart, Multiple files (plus CLI scripting) • Uses DBMS_CLOUD under the covers • See failed rows, import logs Step-by-step Example “Loading data from OSS to Oracle Autonomous Cloud Services with SQL Developer“
18. Data Pump On Prem -> Cloud • Local export is ran • DMP is copied to OSS (or) • Use IMP wizard with a DMP already in OSS Step-by-step Example “Data Pump your Data to the Oracle Autonomous Database“
19. Database Copy (or Drag & Drop) • Any Oracle Database Connection • Pick tables, objects, move to Autonomous Instance • Ideal for smaller data sets, 16k rows/sec in a local test Step-by-step Example “Using Database Copy to move data to Oracle Autonomous Database“
20. Whitepapers • Basic Overview • Sybase ASE Deep Dive Non Oracle Database Migrations • Wizard/Project • SQL Server, DB2, Sybase, Teradata, MySQL, Redshift (Autonomous Data Warehouse) • Capture & Convert Schema • Assist with stored proc/view translations
21. Capture Captures Metadata schemas and tables AWS Convert Redshift Datatypes are mapped to Oracle Datatypes. Redshift Object names are converted to Oracle names. The Column Defaults that use Redshift functions are replaced by their Oracle equivalents. Generate Generate Schemas & DDLs based on the Converted Metadata. Deploy Deploy the Generated Schemas & DDLs. Copy Data Unload data from Redshift tables to Amazon Storage S3. Copy data from Amazon Storage to ADW. Redshift => Autonomous Data Warehouse
22. SQLcl • Modern command-line interface • Auto complete SQL syntax • Command history • Output json, csv, html, inserts, xml… • Extended Liquibase support • Scripting friendly • No Oracle Home required • OCI & OSS Integration https://www.oracle.com/database/technologies/appdev/sqlcl.html SQL Command Line
23. • Easy to use LOAD command • Integration with OCI & OSS • CLI is ideal for automated CI/CD Data Loading with SQLcl Step-by-step Example “SQLcl version 20.3 and OCI Support“
24. More SQLcl: CloudShell & Liquibase • Immediate, familiar access from your browser • Enhanced LiquiBase Support • Automated changeSet updates! Info & Demo “SQLcl and OCI Cloud Shell – Get Up and Running Quickly with your Autonomous Oracle Database”
25. • Microservices for your Oracle Database • Declaratively defined using SQL, PL/SQL • Define via PL/SQL API, SQL Dev or APEX • Auto REST enable tables and views • Create custom REST services • Document Store (SODA for REST) • Database Management REST APIs • REST Enabled SQL Oracle REST Data Services https://www.oracle.com/rest HTTPS Results JSON SQL Meta Data Repo
26. • AutoREST Batchload CSV • SODA for REST • REST Enabled SQL • Customer RESTful Web Services Data Loading with ORDS ORDS is available out-of-the-box with Oracle Autonomous Database More on ORDS and Autonomous “Everything you need to know about ORDS on Autonomous Database“
27. • Enable Table or View for REST • POST CSV BatchLoad CSV via REST Step-by-step Example “Batch Loading CSV to a TABLE in Oracle Autonomous Database using AUTOREST API“ 10,000,000 rows loaded in < 28 seconds
28. • PUT to create a new Collection • POST to add multiple Documents SODA for REST Step-by-step Example “Fastest Way to Upload a bunch of Documents to your Oracle JSON Collection”
29. • Customer supplied SQL or PL/SQL • GET/PUT/POST/DELETE data • Secured with OAuth2 or DB Auth RESTful Web Services Step-by-step Example “Building a Web Service for Uploading and Downloading Files: The Video“
30. • POST statements or scripts • Ad Hoc, supports binds • {json} responses REST Enabled SQL Docs & Examples “ORDS 20.3 Docs REST-Enabled SQL Service“
31. Resources ORDS 20.3 Docs REST-Enabled SQL Service Building a Web Service for Uploading and Downloading Files: The Video Fastest Way to Upload a bunch of Documents to your Oracle JSON Collection Batch Loading CSV to a TABLE in Oracle Autonomous Database using AUTOREST API Everything you need to know about ORDS on Autonomous Database SQLcl and OCI Cloud Shell – Get Up and Running Quickly with your Autonomous Oracle Database SQLcl version 20.3 and OCI Support Using Database Copy to move data to Oracle Autonomous Database Data Pump your Data to the Oracle Autonomous Database Loading data from OSS to Oracle Autonomous Cloud Services with SQL Developer How to Import from Excel to Oracle with SQL Developer Use any AWS S3 compatible object store with Autonomous Database