How to do EXPORT / IMPORT between #Exasol and #Oracle

The commands IMPORT and EXPORT provide an easy way to transfer data between Exasol and other data sources like an Oracle database. You may at first get this error message: Oracle instant client not available. Please ask your administrator to install it via EXAoperation.

Here’s how to do that yourself without having to ask your admin 🙂

exaoperation URL

I’m using the free Exasol Community Edition. The highlighted part shows how to access ExaOperation, then click Software:

exaoperation software

Now go to Oracle Downloads and pick the right Instant Client as shown on the picture.

Oracle Instant Client download

Back in ExaOperation, you upload that ZIP-file from where you downloaded it on your local machine, then hit Submit:

ExaOperation Upload

And that’s already it – you may check with Show Installation History.

First I prepare my Oracle sandbox:

 

λ sqlplus sys/oracle@orcl as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 29 13:39:04 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create user ora_user identified by ora_password;

User created.

SQL> grant dba to ora_user;

Grant succeeded.

SQL> create table ora_user.ora_table (n number, m varchar2(20));

Table created.

SQL> insert into ora_user.ora_table values (1,'JUST SOME TEXT');

1 row created.

SQL> commit;

Commit complete.

The connect descriptor orcl above is resolved by a textfile named tnsnames.ora:

λ type c:\oracle\instantclient_12_2\network\admin\tnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Just to clarify: I have two different Instant Clients involved here, one for the Exasol database platform (Linux) and one for my local machine (Windows). The latter is not mandatory for the IMPORT command to work. I just find it convenient to be able to connect to the Oracle database from the same machine where I connect to Exasol. Also installing SQL*Plus is not mandatory.
Now I continue to prepare my Exasol sandbox:

C:\Program Files (x86)\EXASOL\EXASolution-6.0\EXAplus
λ exaplusx64 -c 192.168.56.101:8563 -u sys -p exasol -lang EN
EXAplus 6.0.8 (c) EXASOL AG

Friday, June 29, 2018 3:09:10 PM CEST
Connected to database EXAone as user sys.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA> create schema exa_schema;
EXA: create schema exa_schema;

Rows affected: 0

SQL_EXA> create table exa_schema.exa_table (col1 double, col2 varchar(30));
EXA: create table exa_schema.exa_table (col1 double, col2 varchar(30));

Rows affected: 0

SQL_EXA> insert into exa_schema.exa_table values (2,'ANOTHER TEXT');
EXA: insert into exa_schema.exa_table values (2,'ANOTHER TEXT');

Rows affected: 1

Now there’s one table in Exasol and a similar table in Oracle. Next step is to create a connection from Exasol to Oracle:

create or replace connection ora_conn to
'(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )'
user 'ora_user' identified by 'ora_password';

Notice the highlighted part that is identical to the tnsnames.ora content highlighted above. Now we are ready to transfer data between the two databases. Oracle to Exasol:

SQL_EXA> import into exa_schema.exa_table from ora at ora_conn table ora_user.ora_table;
EXA: import into exa_schema.exa_table from ora at ora_conn table ora_user.o...

Rows affected: 1

SQL_EXA> select * from exa_schema.exa_table;
EXA: select * from exa_schema.exa_table;

COL1              COL2
----------------- ------------------------------
                2 ANOTHER TEXT
                1 JUST SOME TEXT

2 rows in resultset.

The other way: Exasol to Oracle

SQL_EXA> export exa_schema.exa_table into ora at ora_conn table ora_user.ora_table;
EXA: export exa_schema.exa_table into ora at ora_conn table ora_user.ora_ta...

Rows affected: 2

Let’s check how that looks at the Oracle side now:

λ sqlplus sys/oracle@orcl as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 29 15:24:52 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from ora_user.ora_table;

         N M
---------- --------------------
         1 JUST SOME TEXT
         2 ANOTHER TEXT
         1 JUST SOME TEXT

Easy, isn’t it? Some more examples:

SQL_EXA> import into exa_schema.exa_table (col2) from ora at ora_conn 
                 statement 'select m from ora_user.ora_table where n=2';
EXA: import into exa_schema.exa_table (col2) from ora at ora_conn statement...

Rows affected: 1

SQL_EXA> select * from exa_schema.exa_table;
EXA: select * from exa_schema.exa_table;

COL1              COL2
----------------- ------------------------------
                2 ANOTHER TEXT
                1 JUST SOME TEXT
                  ANOTHER TEXT

3 rows in resultset.
SQL_EXA> export (select col1 from exa_schema.exa_table where col2 like 'JUST%') into ora at ora_conn
            statement 'insert into ora_user.ora_table (n) values (:1)';
EXA: export (select col1 from exa_schema.exa_table where col2 like 'JUST%')...

Rows affected: 1
SQL> select * from ora_user.ora_table;

         N M
---------- --------------------
         1 JUST SOME TEXT
         1
         2 ANOTHER TEXT
         1 JUST SOME TEXT

Check the fine manual for some more details: https://www.exasol.com/portal/display/DOC/Database+User+Manual

, ,

Leave a comment

#Exasol’s annual conference in Berlin #XP18

We have had our annual conference Xperience in Berlin again – it was an amazing event with a packed agenda full of customer success stories and technical news. This year at the Umspannwerk.

Umspannwerk Berlin

My assumption is that we will need a larger location next year because of our constant growth – it was a bit tight already this time 🙂

Many great talks have been presented, so just a few highlights from my point of view:

Our CEO Aaron Auld delivered an inspiring kick-off talk together with CTO Mathias Golombek

Keynote Aaron & Mathias

I liked in particular when he told us that boring old reliable technology like RDBMS is still relevant and worth to be improved – this is what we do for a living in the end.

Great success story by Magnus Slind-Näslund from Dailymotion who reported significant performance improvements due to the usage of Exasol!

dailymotion

Similar the experience shared by Steve Templeton from Arby’s Restaurant Group who came all the way from the US to tell us:

arbys

One highlight on day 2 was the presentation by Jens Graupmann about our future roadmap and what’s new in version 6.1 of Exasol

Jens Graupmann

Another was the excellent talk and live demo of DBVisualizer by Thomas Bestfleisch:

Thomas Bestfleisch

We learned that Exaplus will be replaced by DBVisualizer as our standard SQL client starting from version 6.1 and for good reason, because the latter is far more powerful as Thomas showed.

Many more great presentations were to be seen of course. And one common denominator from all the customer presentations and all conversations with customers I encountered was how satisfied they are with the Exasol technology, the striking performance and especially also with the attitude our staff demonstrates towards them.

Be it with consulting or support, our customers feel they are valued by us and their demands and (rare) complaints are taken serious. This is something we must keep up in the future throughout our growth! Aaron rightly emphasized that in his goodbye talk at the end of this great event.

 

,

Leave a comment

Users, schemas & privileges in #Exasol

Exasol Logo

In Exasol, a database user may own multiple schemas – or even none at all. I connect to my Community Edition to show that:

C:\Users\uh>cd \Program Files (x86)\EXASOL\EXASolution-6.0\EXAplus

C:\Program Files (x86)\EXASOL\EXASolution-6.0\EXAplus>exaplusx64 -c 192.168.56.101:8563 -u sys -p exasol -lang EN

EXAplus 6.0.8 (c) EXASOL AG

Wednesday, May 23, 2018 3:28:29 PM CEST
Connected to database EXAone as user sys.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA> create user adam identified by adam;
EXA: create user adam identified by adam;

Rows affected: 0

SQL_EXA> grant dba to adam;
EXA: grant dba to adam;

Rows affected: 0

SQL_EXA> select user_name from exa_dba_users;
EXA: select user_name from exa_dba_users;

USER_NAME
------------------------------------------------------------
SYS
ADAM

2 rows in resultset.

SQL_EXA> select schema_owner,schema_name from exa_schemas;
EXA: select schema_owner,schema_name from exa_schemas;

SCHEMA_OWNER
-------------------------------------------------------------
SCHEMA_NAME
-------------------------------------------------------------
SYS
RETAIL

1 row in resultset.

SQL_EXA> connect adam/ADAM;

Wednesday, May 23, 2018 3:34:42 PM CEST
Connected to database EXAone as user adam.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA> create table t1 (n number);
EXA: create table t1 (n number);
Error: [42000] no schema specified or opened or current schema has been dropped [line 1, column 27] (Session: 1601269589413551548)
SQL_EXA> open schema adam;
EXA: open schema adam;
Error: [42000] schema ADAM not found [line 1, column 13] (Session: 1601269589413551548)

Demo user adam has the DBA role granted but there is no adam schema yet. I need to create it first:

EXA: create schema adam;

Rows affected: 0

SQL_EXA> open schema adam;
EXA: open schema adam;

Rows affected: 0

SQL_EXA> create table t1 (n number);
EXA: create table t1 (n number);

Rows affected: 0

SQL_EXA> create schema adam2;
EXA: create schema adam2;

Rows affected: 0

SQL_EXA> create table adam2.t2 (n number);
EXA: create table adam2.t2 (n number);

Rows affected: 0

SQL_EXA> select table_schema,table_name from exa_user_tables;
EXA: select table_schema,table_name from exa_user_tables;

TABLE_SCHEMA
--------------------------------------------------------
TABLE_NAME
--------------------------------------------------------
ADAM
T1
ADAM2
T2

2 rows in resultset.

As you see, user adam has now two schemas with different tables in them. Now briefly to privileges:

SQL_EXA> create user fred identified by fred;
EXA: create user fred identified by fred;

Rows affected: 0

SQL_EXA> grant create session to fred;
EXA: grant create session to fred;

Rows affected: 0

SQL_EXA> grant select on adam.t1 to fred;
EXA: grant select on adam.t1 to fred;

Rows affected: 0

SQL_EXA> connect fred/FRED;

Wednesday, May 23, 2018 3:53:34 PM CEST
Connected to database EXAone as user fred.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA> select * from adam.t1;
EXA: select * from adam.t1;

N
-----------------

0 rows in resultset.

SQL_EXA> select * from adam2.t2;
EXA: select * from adam2.t2;
Error: [42500] insufficient privileges: SELECT on table T2 (Session: 1601270776421928841)
SQL_EXA> connect adam/ADAM;

Wednesday, May 23, 2018 3:54:33 PM CEST
Connected to database EXAone as user adam.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA> create role allonadam2;
EXA: create role allonadam2;

Rows affected: 0

SQL_EXA> grant all on adam2 to allonadam2;
EXA: grant all on adam2 to allonadam2;

Rows affected: 0

SQL_EXA> grant allonadam2 to fred;
EXA: grant allonadam2 to fred;

Rows affected: 0

SQL_EXA> connect fred/FRED;

Wednesday, May 23, 2018 3:55:54 PM CEST
Connected to database EXAone as user fred.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA> select * from adam2.t2;
EXA: select * from adam2.t2;

N
-----------------

0 rows in resultset.

SQL_EXA> drop table adam2.t2;
EXA: drop table adam2.t2;
Error: [42500] insufficient privileges for dropping table (Session: 1601270923042332982)

That’s because ALL contains ALTER, DELETE, EXECUTE, INSERT, SELECT and UPDATE but not DROP which can be confirmed using EXA_DBA_OBJ_PRIVS.

1 Comment

%d bloggers like this: