CODEWITHSUNDEEP
clickhouse
Bubbles TrailerPark
Bubbles TrailerPark

Reputation: 1

Clickhouse Materialized View not inserting new data

So, I created this table:

CREATE TABLE db123.UserData
(
    RecordDate          Date,
    RecordTime          DateTime,
    UserID              UInt64,
    NetID               UInt64,
    StallID             UInt64,
    Email               Nullable(String),
    Name                Nullable(String),
    LastName            Nullable(String),
    Gender              Nullable(String),
    DateOfBirth         Nullable(String),
    Address             Nullable(String),
    CountryID           Nullable(UInt64),
    CountryName         Nullable(String),
    Phone               Nullable(String),
    AlternativePhone    Nullable(String),
    Language            Nullable(String),
    Timezone            Nullable(Int16),
    IsTimezoneSet       Nullable(UInt8),
    CurrencyID          Nullable(UInt16),
    Currency            Nullable(String),
    RegistrationDate    Nullable(DateTime),
    RegistrationIP      Nullable(String),
    AffiliateClickID    Nullable(String),
    AffiliateID         Nullable(String),
    AffiliateSystem     Nullable(String),
    ManagerID           Nullable(Int64),
    Level               Nullable(UInt8),
    ToSAccepted         Nullable(UInt8),
    DuplicateLevel      Nullable(UInt32),
    PhoneVerified       Nullable(UInt8),
    EmailVerified       Nullable(UInt8),
    EmailAgree          Nullable(UInt8),
    SmsAgree            Nullable(UInt8),
    PEP                 UInt8 DEFAULT 0,
    IsStreamer          Nullable(UInt8),
    LastLogoutDate      Nullable(DateTime),
    FirstDepositDate    Nullable(DateTime),
    LastDepositDate     Nullable(DateTime),
    LastPetDate         Nullable(DateTime),
    Status              Nullable(Int8),
    LastLoginDate       Nullable(DateTime),
    LastLoginIP         Nullable(String),
    LockedWithdrawals   Nullable(Int64),
    LockedBalance       Nullable(Int64),
    LastCreditDate      Nullable(DateTime),
    FirstCreditDate     Nullable(DateTime),
    IsLoyalty           Nullable(UInt8),
    SelfExclusion       Nullable(String),
    ForbidFunding       Nullable(UInt8) DEFAULT 0
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(RecordDate)
ORDER BY (UserID, RecordDate)
SETTINGS index_granularity = 8192;

And then manually populated it:

INSERT INTO db123.UserData (
    RecordDate,
    RecordTime,
    UserID,
    NetID,
    StallID,
    Email,
    Name,
    LastName,
    Gender,
    DateOfBirth,
    Address,
    CountryID,
    CountryName,
    Phone,
    AlternativePhone,
    Language,
    Timezone,
    IsTimezoneSet,
    CurrencyID,
    Currency,
    RegistrationDate,
    RegistrationIP,
    AffiliateClickID,
    AffiliateID,
    AffiliateSystem,
    ManagerID,
    Level,
    ToSAccepted,
    DuplicateLevel,
    PhoneVerified,
    EmailVerified,
    EmailAgree,
    SmsAgree,
    PEP,
    IsStreamer,
    LastLogoutDate,
    Status,
    LastLoginDate,
    LastLoginIP,
    LockedWithdrawals,
    LockedBalance,
    LastCreditDate,
    FirstCreditDate,
    IsLoyalty,
    SelfExclusion,
    ForbidFunding
)
SELECT 
    uh1.RecordDate,
    uh1.RecordTime,
    uh1.UserID,
    uh1.NetID,
    uh1.StallID,
    uh1.Email,               
    uh1.Name,                
    uh1.LastName,           
    uh1.Gender,    
    uh1.DateOfBirth,
    uh1.Address,
    uh1.CountryID,
    cn.CountryName,
    uh1.Phone,
    uh1.AlternativePhone,
    uh1.Language,
    uh1.Timezone,
    uh1.IsTimezoneSet,
    uh1.CurrencyID,
    cur.Currency,
    uh1.RegistrationDate,
    uh1.RegistrationIP,
    uh1.AffiliateClickID,
    uh1.AffiliateID,
    uh1.AffiliateSystem,
    uh1.ManagerID,
    uh1.Level,
    uh1.ToSAccepted,
    uh1.DuplicateLevel,
    uh1.PhoneVerified,
    uh1.EmailVerified,
    uh1.EmailAgree,
    uh1.SmsAgree,
    uh1.PEP,
    uh1.IsStreamer,
    uh1.LastLogoutDate,
    uh1.Status,
    uh1.LastLoginDate,
    uh1.LastLoginIP,
    uh1.LockedWithdrawals,
    uh1.LockedBalance,
    uh1.LastCreditDate,
    uh1.FirstCreditDate,
    uh1.IsLoyalty,
    uh1.SelfExclusion,
    uh1.ForbidFunding
FROM 
    (
    SELECT 
        uh1.*,
        ROW_NUMBER() OVER (PARTITION BY uh1.UserID ORDER BY uh1.LastUpdated DESC) AS RowNum
    FROM db123.UserHistory uh1
    ) uh1
JOIN 
    (
    SELECT 
        UserID, 
        MAX(LastUpdated) AS MaxLastUpdated
    FROM db123.UserHistory
    GROUP BY UserID
    ) uh2
ON uh1.UserID = uh2.UserID AND uh1.LastUpdated = uh2.MaxLastUpdated
LEFT JOIN 
    (
    SELECT
        ID AS CountryID,
        JSONExtractString(Name, 'en') AS CountryName
    FROM db123.CountriesNew
    ) cn
ON uh1.CountryID = cn.CountryID
LEFT JOIN
    (
    SELECT
        ID as CurrID,
        Name as Currency
    FROM db123.Currencies
    ) cur
ON uh1.CurrencyID = cur.CurrID
WHERE uh1.RowNum = 1 AND uh1.UserID NOT IN (SELECT UserID FROM db123.UserData);

And that worked no problem. So, I wanted to create a MV out of it to automatically add the latest rows, but it's not working. Here is the MV:

CREATE MATERIALIZED VIEW db123.vw_UserData_New 
TO db123.UserData
AS
SELECT 
    uh1.RecordDate,
    uh1.RecordTime,
    uh1.UserID,
    uh1.NetID,
    uh1.StallID,
    uh1.Email,               
    uh1.Name,                
    uh1.LastName,           
    uh1.Gender,    
    uh1.DateOfBirth,
    uh1.Address,
    uh1.CountryID,
    cn.CountryName,
    uh1.Phone,
    uh1.AlternativePhone,
    uh1.Language,
    uh1.Timezone,
    uh1.IsTimezoneSet,
    uh1.CurrencyID,
    cur.Currency,
    uh1.RegistrationDate,
    uh1.RegistrationIP,
    uh1.AffiliateClickID,
    uh1.AffiliateID,
    uh1.AffiliateSystem,
    uh1.ManagerID,
    uh1.Level,
    uh1.ToSAccepted,
    uh1.DuplicateLevel,
    uh1.PhoneVerified,
    uh1.EmailVerified,
    uh1.EmailAgree,
    uh1.SmsAgree,
    uh1.PEP,
    uh1.IsStreamer,
    uh1.LastLogoutDate,
    uh1.Status,
    uh1.LastLoginDate,
    uh1.LastLoginIP,
    uh1.LockedWithdrawals,
    uh1.LockedBalance,
    uh1.LastCreditDate,
    uh1.FirstCreditDate,
    uh1.IsLoyalty,
    uh1.SelfExclusion,
    uh1.ForbidFunding
FROM 
    (
    SELECT 
        uh1.*,
        ROW_NUMBER() OVER (PARTITION BY uh1.UserID ORDER BY uh1.LastUpdated DESC) AS RowNum
    FROM db123.UserHistory uh1
    ) uh1
JOIN 
    (
    SELECT 
        UserID, 
        MAX(LastUpdated) AS MaxLastUpdated
    FROM db123.UserHistory
    GROUP BY UserID
    ) uh2
ON uh1.UserID = uh2.UserID AND uh1.LastUpdated = uh2.MaxLastUpdated
LEFT JOIN 
    (
    SELECT
        ID AS CountryID,
        JSONExtractString(Name, 'en') AS CountryName
    FROM db123.CountriesNew
    ) cn
ON uh1.CountryID = cn.CountryID
LEFT JOIN
    (
    SELECT
        ID as CurrID,
        Name as Currency
    FROM db123.Currencies
    ) cur
ON uh1.CurrencyID = cur.CurrID
WHERE uh1.RowNum = 1 AND uh1.UserID NOT IN (SELECT UserID FROM db123.UserData);

I am also creating an MV to update these fields as they change in the source feed:

FirstDepositDate Nullable(DateTime), LastDepositDate Nullable(DateTime), LastPetDate Nullable(DateTime)

Basically, I'm trying to perform a stored proc. This is what I'm used to in SQL Server and Redshift etc but CH has been a pain in the backside. I have successfully created MVs before but this one just isn't working and I can't understand why...

Please could someone shed some light on this.

Basically, I'm trying to perform a stored proc. This is what I'm used to in SQL Server and Redshift etc but CH has been a pain in the backside. I have successfully created MVs before but this one just isn't working and I can't understand why...

Please could someone shed some light on this.

Upvotes: 0

Views: 809

Answers (1)

Rich Raposa
Rich Raposa

Reputation: 828

Materialized Views in ClickHouse are populated when rows are inserted into the view's source table. Your particular MV above does not have a source table (you have defined your view as the result of query). So there is no "trigger" to insert new rows into your view.

I'm not sure what your business logic is, but maybe try modifying your FROM clause of your view to:

CREATE MATERIALIZED VIEW db123.vw_UserData_New 
TO db123.UserData
AS
SELECT 
   <whatever>
FROM 
    uh1
JOIN 
    ...

Now uh1 becomes the source table for your view, and when you insert rows into uh1, those same rows will be sent to db123.vw_UserData_New

Upvotes: 0

Related Questions