Temp table ignoring few inserts

opper20opper20 Registered User

Hi Team,

I am trying to load data into a global temp table and after inserting when i do a select it ignores 50% of records.Below is my table structure and projection.When i create a projection with Unsegmented it worked for me.

Table Structure and projection:

CREATE TEMPORARY TABLE POC.ABC_GTT
(
VNCID varchar(48) NOT NULL,
VALID_FROM timestamp(0) NOT NULL,
VALID_TO timestamp(0),
PARTNERID varchar(32),
PARTNERNAME varchar(64),
SERVICEPROVIDERNAME varchar(64),
SERVICEINSTANCEID numeric(25,0),
SERVICEGROUPID numeric(25,0),
COMPANYID varchar(32),
VNCNAME varchar(64),
VNCSTATUS varchar(16),
MBC numeric(25,0),
CLIENTID varchar(48),
SUBCLIENTID varchar(48),
RESELLER char(1),
SITEID varchar(16),
DATACENTERNAME varchar(32),
CLIENTNAME varchar(100),
SUBCLIENTNAME varchar(100),
SITENAME varchar(32),
SERVICEPROVIDERID varchar(48),
ROUTINGDOMAINID varchar(48),
ROUTINGDOMAINNAME varchar(64),
VPNID numeric(18,0),
VPNNAME varchar(64),
MCN varchar(20),
PARTNERASN varchar(64),
SITECITY varchar(32),
SITESTATE char(2),
SITEZIPCODE varchar(12),
SITECOUNTRYCODE char(2),
SITEREGIONCODE varchar(10),
MAXMBC numeric(25,0),
EVENT varchar(50),
ACTIVITY_DESC varchar(500),
INSERTED_TS timestamp(0),
LAST_UPDATE_TS timestamp(0)
) NO PROJECTION;

CREATE PROJECTION POC.ABC_GTT_PRJ
(
VNCID,
VALID_FROM,
VALID_TO,
PARTNERID,
PARTNERNAME,
SERVICEPROVIDERNAME,
SERVICEINSTANCEID,
SERVICEGROUPID,
COMPANYID,
VNCNAME,
VNCSTATUS,
MBC,
CLIENTID,
SUBCLIENTID,
RESELLER,
SITEID,
DATACENTERNAME,
CLIENTNAME,
SUBCLIENTNAME,
SITENAME,
SERVICEPROVIDERID,
ROUTINGDOMAINID,
ROUTINGDOMAINNAME,
VPNID,
VPNNAME,
MCN,
PARTNERASN,
SITECITY,
SITESTATE,
SITEZIPCODE,
SITECOUNTRYCODE,
SITEREGIONCODE,
MAXMBC,
EVENT,
ACTIVITY_DESC,
INSERTED_TS,
LAST_UPDATE_TS
)
AS
SELECT VNCID,
VALID_FROM,
VALID_TO,
PARTNERID,
PARTNERNAME,
SERVICEPROVIDERNAME,
SERVICEINSTANCEID,
SERVICEGROUPID,
COMPANYID,
VNCNAME,
VNCSTATUS,
MBC,
CLIENTID,
SUBCLIENTID,
RESELLER,
SITEID,
DATACENTERNAME,
CLIENTNAME,
SUBCLIENTNAME,
SITENAME,
SERVICEPROVIDERID,
ROUTINGDOMAINID,
ROUTINGDOMAINNAME,
VPNID,
VPNNAME,
MCN,
PARTNERASN,
SITECITY,
SITESTATE,
SITEZIPCODE,
SITECOUNTRYCODE,
SITEREGIONCODE,
MAXMBC,
EVENT,
ACTIVITY_DESC,
INSERTED_TS,
LAST_UPDATE_TS
FROM POC.ABC_GTT
ORDER BY VNCID,
VALID_FROM,
VALID_TO
SEGMENTED BY hash(VNCID, VALID_FROM, VALID_TO, PARTNERID, PARTNERNAME, SERVICEPROVIDERNAME, SERVICEINSTANCEID, SERVICEGROUPID, COMPANYID, VNCNAME, VNCSTATUS, MBC, CLIENTID, SUBCLIENTID, RESELLER, SITEID, DATACENTERNAME, CLIENTNAME, SUBCLIENTNAME, SITENAME, SERVICEPROVIDERID, ROUTINGDOMAINID, ROUTINGDOMAINNAME, VPNID, VPNNAME, MCN, PARTNERASN, SITECITY, SITESTATE, SITEZIPCODE, SITECOUNTRYCODE, SITEREGIONCODE) ALL NODES KSAFE 1;

Comments

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    Can you show us how you are loading the data? Note that the default behavior of temp tables is to remove all table data after each commit.

  • opper20opper20 Registered User

    HI Jim,Any update on this issue.We are facing a simmilar issue with one of our table today and it loads only 50% of data and rest ignores...We are facing this only with Temp Tables and not with Regular tables...

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    @opper20 - Can you show the steps? Including the CREATE GLOBAL TEMP TABLE statement?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file