SQLImporter ERROR if table already exists


#1

The online Docs report :
"If the table doesn’t exist it will create the table in MapD Core."
I created an empty table using the FIXED format to optimize it, but I have :

ERROR SQLImporter:executeMapDCommand:433 - SQL Execute failed - TMapDException(error_msg:Exception: Table already exists.)

I already tried using -tr ( flag for truncating the table) without success.

MapD-3.1.1, Centos 7.2


#2

Hi,

I am guessing here as not a lot of info here to go on, but you may be running into a case sensitivity issue on table names,

Would it be possible to share the details of the command you are running and a \t and \d <table> of the table you are trying to load.

regards


#3

Your guessing hit the target dwayneberry !

I created the table name in uppercase, but when I tried to create the name in lowercase the problem disappeared.
It is not clear to me what are the reasons of this behavior, anyway, “using lowercase name”, lesson learnt.

After creating the table in lowercase SQLImporter is working fine with the -tr flag, but it is showing another problem:

I created the table with this DDL and some FIXED ENCODING columns:

CREATE TABLE IF NOT EXISTS <tablename> (
  aa                                      INT,
  mm                                      INT,
  dtdoc                                   INT NOT NULL,
  mag                                     TEXT ENCODING DICT(8),
  cau                                     SMALLINT,
  codfor                                  TEXT ENCODING DICT(16),
  codpro                                  INT NOT NULL,
  codcli                                  INT NOT NULL,
  trans_progr_a                           BIGINT,
  trans_v                                 INT NOT NULL,
  progr                                   INT NOT NULL,
  qta                                     DECIMAL(14,4),
  prz_ces                                 DECIMAL(10,4),
  colli                                   DECIMAL(14,4),
  tipo                                    TEXT ENCODING DICT(8),
  voce                                    INT,
  stato                                   TEXT ENCODING DICT(8),
  pedane                                  INT,
  c_n_1                                   DECIMAL(13,5),
  c_n_4                                   DECIMAL(13,5),
  c_n_6                                   DECIMAL(13,5),
  tipo_mov                                TEXT ENCODING DICT(8),
  colli_mancanti                          DECIMAL(9,2),
  area_cedis                              TEXT ENCODING DICT(8),
  peso                                    DECIMAL(11,2), 
  lista                                   INT,
  nrig                                    INT,
  handling                                DECIMAL(16,8),
  trasporto                               DECIMAL(16,8),
  immobile                                DECIMAL(16,8)
  );

obtaining:

    mapdql> \d fh206_fg894_fg510_v3_ac
CREATE TABLE fh206_fg894_fg510_v3_ac (
aa INTEGER,
mm INTEGER,
dtdoc INTEGER NOT NULL,
mag TEXT ENCODING DICT(8),
cau SMALLINT,
codfor TEXT ENCODING DICT(16),
codpro INTEGER NOT NULL,
codcli INTEGER NOT NULL,
trans_progr_a BIGINT,
trans_v INTEGER NOT NULL,
progr INTEGER NOT NULL,
qta DECIMAL(14,4),
prz_ces DECIMAL(10,4),
colli DECIMAL(14,4),
tipo TEXT ENCODING DICT(8),
voce INTEGER,
stato TEXT ENCODING DICT(8),
pedane INTEGER,
c_n_1 DECIMAL(13,5),
c_n_4 DECIMAL(13,5),
c_n_6 DECIMAL(13,5),
tipo_mov TEXT ENCODING DICT(8),
colli_mancanti DECIMAL(9,2),
area_cedis TEXT ENCODING DICT(8),
peso DECIMAL(11,2),
lista INTEGER,
nrig INTEGER,
handling DECIMAL(16,8),
trasporto DECIMAL(16,8),
immobile DECIMAL(16,8))

But after running SQLImporter it drops the table correctly, and then recreate it with DIFFERENT ENCODING!

2017-07-04 12:47:59 INFO  SQLImporter:executeMapDCommand:428 -  run comamnd :Drop table fh206_fg894_fg510_v3_ac

2017-07-04 12:48:02 INFO  SQLImporter:executeMapDCommand:428 -  run comamnd :Create table fh206_fg894_fg510_v3_ac(
aa INTEGER,
mm INTEGER,
dtdoc INTEGER,
mag TEXT ENCODING DICT,
cau INTEGER,
codfor TEXT ENCODING DICT,
codpro INTEGER,
codcli INTEGER,
trans_progr_a BIGINT,
trans_v INTEGER,
progr INTEGER,
qta DECIMAL(14,4),
prz_ces DECIMAL(10,4),
colli DECIMAL(14,4),
tipo TEXT ENCODING DICT,
voce INTEGER,
stato TEXT ENCODING DICT,
pedane INTEGER,
c_n_1 DECIMAL(13,5),
c_n_4 DECIMAL(13,5),
c_n_6 DECIMAL(13,5),
tipo_mov TEXT ENCODING DICT,
colli_mancanti DECIMAL(9,2),
area_cedis TEXT ENCODING DICT,
peso DECIMAL(11,2),
lista INTEGER,
nrig INTEGER,
handling DECIMAL(16,8),
trasporto DECIMAL(16,8),
immobile_MM DECIMAL(16,8),
immobile_VET DECIMAL(16,8)
)

The final TABLE has different encoding:

mapdql> \d fh206_fg894_fg510_v3_ac
CREATE TABLE fh206_fg894_fg510_v3_ac (
aa INTEGER,
mm INTEGER,
dtdoc INTEGER,
mag TEXT ENCODING DICT(32),
cau INTEGER,
codfor TEXT ENCODING DICT(32),
codpro INTEGER,
codcli INTEGER,
trans_progr_a BIGINT,
trans_v INTEGER,
progr INTEGER,
qta DECIMAL(14,4),
prz_ces DECIMAL(10,4),
colli DECIMAL(14,4),
tipo TEXT ENCODING DICT(32),
voce INTEGER,
stato TEXT ENCODING DICT(32),
pedane INTEGER,
c_n_1 DECIMAL(13,5),
c_n_4 DECIMAL(13,5),
c_n_6 DECIMAL(13,5),
tipo_mov TEXT ENCODING DICT(32),
colli_mancanti DECIMAL(9,2),
area_cedis TEXT ENCODING DICT(32),
peso DECIMAL(11,2),
lista INTEGER,
nrig INTEGER,
handling DECIMAL(16,8),
trasporto DECIMAL(16,8),
immobile_MM DECIMAL(16,8),
immobile_VET DECIMAL(16,8))

How can I keep the same encoding when I rewrite the table with the SQLImporter utility?


#4

Hi,

To use an table with specific fixed encoding with SQLImporter you must manually create the table prior to running SQLimporter. Do not set the truncate option -tr as it will drop and recreate the table (which will lose your specially defined columns).

So the steps would be

  1. Create table with fixed encoding
  2. Run SQLImporter with no truncate option

regards


#5

Your suggestions make sense, dwayneberry.

For some reason I was expecting something similar to the “TRUNCATE” function that we found in the other RDBMs, where the table structure is kept.

Sure a TRUNCATE function like that would be useful, (maybe with another name because truncate is used by a mathematical function in MapD).

Anyway, thank you, problem solved.