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

Using External Tables to Load and Unload Data

D7b6e701f0155fc189bbca6c89223b3c?s=47 Chris
August 01, 2019

Using External Tables to Load and Unload Data

D7b6e701f0155fc189bbca6c89223b3c?s=128

Chris

August 01, 2019
Tweet

Transcript

  1. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Your SQL Office Hours session will begin soon… Using external tables to load & unload data with Chris Saxon, @ChrisRSaxon & @SQLDaily https://www.youtube.com/c/TheMagicofSQL https://blogs.oracle.com/sql
  2. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Welcome to Ask TOM Office Hours!
  3. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | create table t_ext ( … ) organization external ( default directory tmp location ( 'sales.csv' ) ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  4. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales.csv select * from t_ext;
  5. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales_20180101.csv sales_20180102.csv sales_20180103.csv select * from t_ext; alter table t_ext location ( '…20180101.csv' ); alter table t_ext location ( '…20180102.csv' ); alter table t_ext location ( '…20180103.csv' );
  6. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales_20180101.csv sales_20180102.csv sales_20180103.csv select * from t_ext external modify ( location ( '…20180101.csv' ) );
  7. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales_20180101.csv sales_20180102.csv sales_20180103.csv select * from t_ext external modify ( location ( '…20180102.csv' ) );
  8. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales_20180101.csv sales_20180102.csv sales_20180103.csv select * from external ( ( <cols> ) default directory … location ('…') )
  9. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | create table t_ext ( … ) organization external ( type oracle_datapump default directory tmp location ( 't.dmp' ) ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  10. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | create table t_ext ( … ) organization external ( type oracle_datapump default directory tmp location ( 't.dmp' ) ) as select * from … ; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Not expdp/impdp compatible!
  11. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales.dmp create table ext ( … ) … ( … oracle_datapump ) as select * from sales;
  12. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales.dmp select * from ext;
  13. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | create table t_ext ( … ) organization external ( … ) partition by range ( … ) ( partition p0 values less than ( … ) location ( '…201901.dmp' ), partition p1 values less than ( … ) location ( '…201902.dmp' ) ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  14. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | create table t_ext ( … ) organization external ( … ) partition by range ( … ) ( partition p0 values less than ( … ) location ( '…201901.dmp' ), partition p1 values less than ( … ) location ( '…201902.dmp' ) ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  15. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales create table as select
  16. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales sales_ext create table as select
  17. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales sales_ext
  18. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | create view sales_all as select * from sales_ext union all select * from sales blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  19. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | create table t_ext ( … ) external partition attributes ( … ) partition by range ( … ) ( partition ext values less than ( … ) external location ( '….dmp' ), partition int values less than ( … ) ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  20. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | create table t_ext ( … ) external partition attributes ( … ) partition by range ( … ) ( partition ext values less than ( … ) external location ( '….dmp' ), partition int values less than ( … ) ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  21. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales create table as select
  22. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon create table as select sales
  23. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales
  24. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Sounds Awesome! Ryan McGuire / Gratisography blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon There are some caveats…
  25. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Hybrid Partitioned Tables Restrictions •Partial indexes only (local or global) •No unique indexes! •Constraints must be RELY DISABLE •List & range partitioning only –NOT automatic or interval partitioning blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  26. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | DEMO
  27. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Now it's over to you!
  28. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | asktom.oracle.com #AskTOMOfficeHours Ryan McGuire / Gratisography