Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Loading Data into your Oracle (Cloud) Database

thatjeffsmith
December 10, 2020

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.

thatjeffsmith

December 10, 2020
Tweet

More Decks by thatjeffsmith

Other Decks in Programming

Transcript

  1. Database Tool Overview
    Loading Data to the Oracle Cloud

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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.

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  10. • DBMS_CLOUD Scripts
    • Relational data to new or existing tables
    • Files to JSON Collections
    Data Loading with SQL Developer Web

    View full-size slide

  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“

    View full-size slide

  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”

    View full-size slide

  13. Loading JSON Documents
    • JSON Document Store
    • One or more files to a collection
    • Point, click…query and go!
    Scheduled for version 20.4

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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“

    View full-size slide

  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“

    View full-size slide

  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“

    View full-size slide

  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“

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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“

    View full-size slide

  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”

    View full-size slide

  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

    View full-size slide

  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“

    View full-size slide

  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

    View full-size slide

  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”

    View full-size slide

  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“

    View full-size slide

  30. • POST statements or scripts
    • Ad Hoc, supports binds
    • {json} responses
    REST Enabled SQL
    Docs & Examples “ORDS 20.3 Docs REST-Enabled SQL Service“

    View full-size slide

  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

    View full-size slide