Using External Tables to Load and Unload Data

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