Load data local in file is replacing old data in mariadb columnstore instead of appending new rows

  columnstore, mariadb, mysql, python-3.x

I am loading bulk data using mariadb load data local in file but each time it is replacing new data with the old ones. I need to keep both old and new data in the mariadb columnstore engine data warehouse. Any help regarding this will be highly appreciable.

self.log.info("Bulk inserting rows into MySQL destination...")
with closing(mysql_destination.get_conn()) as conn:
   with closing(conn.cursor()) as cursor:
      cursor.execute(
      "LOAD DATA LOCAL INFILE '%s' INTO "
      "TABLE %s LINES TERMINATED BY 'rn' (%s)" %
      (tmpfile.name,
      self.mysql_destination_table,
      ", ".join(selected_columns))
      )
   conn.commit()
tmpfile.close()

table structure

CREATE TABLE `daily_sales_msr_fact`  (
  `id` int(11) NULL DEFAULT NULL COMMENT 'autoincrement=1',
  `mtime` timestamp(0) NOT NULL DEFAULT current_timestamp ON UPDATE CURRENT_TIMESTAMP,
  `dpid` int(11) NOT NULL,
  `route_id` int(11) NOT NULL,
  `skid` int(11) NOT NULL,
  `prid` int(11) NOT NULL,
  `group` int(11) NOT NULL,
  `family` int(11) NOT NULL,
  `date` date NOT NULL DEFAULT '1990-01-01',
  `sale` double NOT NULL,
  `dprice` double NOT NULL,
  `rprice` double NOT NULL,
  `dcc_price` double NOT NULL,
  `issue` double NOT NULL,
  `return` double NULL DEFAULT NULL,
  `memos` int(11) NOT NULL,
  `vmemos` int(11) NOT NULL,
  `tlp` double NOT NULL,
  `cnc` double NOT NULL,
  `vp` double NOT NULL,
  `mvp` double NOT NULL,
  `p` double NOT NULL,
  `tcc` double NOT NULL,
  `dcc` double NOT NULL,
  `ecnc` double NOT NULL,
  `gt` double NOT NULL,
  `struc` double NOT NULL,
  `semi_struc` double NOT NULL,
  `streetk` double NOT NULL,
  `mass_hrc` double NOT NULL,
  `pop_hrc` double NOT NULL,
  `prem_hrc` double NOT NULL,
  `kaccounts` double NOT NULL,
  `ogrocery` double NOT NULL,
  `snb_cnc` double NULL DEFAULT NULL,
  `pay_n_go` double NULL DEFAULT NULL,
  `shop_n_browse` double NULL DEFAULT NULL,
  `entertainment` double NULL DEFAULT NULL,
  `outlets` int(11) NOT NULL,
  `apps_version` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  `updated` timestamp(0) NULL DEFAULT NULL,
  `visited` int(11) NULL DEFAULT NULL
) ENGINE = Columnstore CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

Source: Python-3x Questions

LEAVE A COMMENT