Hello folks, can anyone help me? I have encountere...
# troubleshooting
k
Hello folks, can anyone help me? I have encountered an issue in my Flink SQL job, I wanted to ensure whether it is due to the ordering of events or it is something im missing. I'm streaming events to Doris using Flink. My source is Kafka. Here are my tables :
Copy code
CREATE TABLE dbo.TreatmentProcedure
(
    AccountId                 INT,
    TreatmentProcedureId      INT,
    LocationId                INT,
    PatientId                 INT,
    PatientEstimate           DECIMAL(19, 4),
    InsuranceEstimate         DECIMAL(19, 4),
    DateOfService             BIGINT,
    UCRFee                    DECIMAL(19, 4),
    AppointmentId             INT,
    DeleteStatusId            INT,
    ProviderId                INT,
    ProcedureCodeId           INT,
    Status                    STRING,
    LastUpdatedOn             BIGINT,
    TreatmentPlanId           INT,
    Fee                       DECIMAL(19, 4),
    CheckedOutDate            BIGINT,
    SpecialtyId               INT,
    IsOrthoProcedure          BOOLEAN,
    ICDCodeIds                STRING,
    Tooth                     STRING,
    ProposedDate              BIGINT,
    B                         INT,
    L                         INT,
    M                         INT,
    O                         INT,
    F                         INT,
    D                         INT,
    I                         INT,
    QuadrantId                INT,
    AppliedMembershipDiscount DECIMAL(19, 4),
    IsDiscountApplied         BOOLEAN,
    DiscountAmount            DECIMAL(19, 4),
    DiscountType              INT,
    SalesTaxPercentage        DECIMAL(5, 2),
    DiscountPercentage        DECIMAL(5, 2),
    BillingOrder              STRING,
    PrimaryDentalInsuranceId  INT,
    PrimaryMedicalInsuranceId INT,
    ProcTime AS PROCTIME(),
    PRIMARY KEY (AccountId, TreatmentProcedureId) NOT ENFORCED
) WITH ( 'connector' = 'kafka', 'topic' = 'dbo.TreatmentProcedure' );

CREATE TABLE dw.FactProcedure
(
    AccountId                   INT,
    TreatmentProcedureId        INT,
    LocationId                  INT,
    PatientId                   INT,
    PatientEstimate             DECIMAL(18, 4),
...
    CheckedOutDate              TIMESTAMP(3),
    AppointmentDate             DATE,
    IsActiveAppointment         BOOLEAN,
    ProviderType                STRING,
    SpecialtyId                 INT,
    CDTCategory                 STRING,
    IsOrthoProcedure            BOOLEAN,
    OrthoCodeWithoutPaymentPlan BOOLEAN,
    ServiceTypeId               INT,
    c_CheckedOutDate            DATE,
    AppointmentProviderId       INT,
    AppointmentProviderType     STRING,
    AppointmentSpecialtyId      INT,
    AppointmentStatus           STRING,
    AppointmentLocationId       INT,
    ICDCodeIds                  STRING,
  ...
    PRIMARY KEY (AccountId, TreatmentProcedureId) NOT ENFORCED
) WITH ( 'connector' = 'doris', 'table' = 'FactProcedure' );
My load scripts looks something like this :
Copy code
INSERT INTO dw.FactProcedure
SELECT src.AccountId,
       src.TreatmentProcedureId,
       src.LocationId,
       src.PatientId,
       src.PatientEstimate,
...
       TO_DATE(CONVERT_TZ(FROM_UNIXTIME(src.CheckedOutDate / 1000000000), 'UTC',
                          da.DWHReportTimeZone))                                                                    AS c_CheckedOutDate,
       COALESCE(sdap.ProviderId, lfa.PrimaryProviderId)                                                             AS AppointmentProviderId,
       COALESCE(sdap.ProviderType, lfa.ProviderType)                                                                AS AppointmentProviderType,
       COALESCE(sdap.SpecialtyId, lfa.SpecialtyId)                                                                  AS AppointmentSpecialtyId,
       COALESCE(ap.Status, lfa.Status)                                                                              AS AppointmentStatus,
       COALESCE(ap.LocationID, lfa.LocationId)                                                                      AS AppointmentLocationId,
       src.ICDCodeIds,
       src.Tooth,
       IF(src.Status = 'Completed' OR src.Status = 'Rejected', FALSE, TRUE)                                            ASIsPlanned,
...
       TO_DATE(CONVERT_TZ(FROM_UNIXTIME(ltsh.CreatedOn / 1000000000), 'UTC',
                        da.DWHReportTimeZone))                                                                AS TreatmentStatusChangeDate
FROM dbo.TreatmentProcedure src
         LEFT JOIN dbo.ProcedureCode pc ON pc.AccountID = src.AccountId AND pc.ProcedureCodeID = src.ProcedureCodeId
           LEFT JOIN dwl.DimProcedureCode FOR SYSTEM_TIME AS OF src.ProcTime AS ldpc
                            ON pc.AccountID IS NULL AND ldpc.AccountId = src.AccountId AND
                               ldpc.ProcedureCodeId = src.ProcedureCodeId
         LEFT JOIN scheduler.Appointment ap ON ap.AccountID = src.AccountId AND ap.AppointmentID = src.AppointmentId
         LEFT JOIN dwl.FactAppointment FOR SYSTEM_TIME AS OF src.ProcTime AS lfa
                            ON ap.AccountID IS NULL AND lfa.AccountId = src.AccountId AND lfa.AppointmentId = src.AppointmentId
         LEFT JOIN staging.DimAppointmentProvider sdap
                   ON sdap.AccountId = src.AccountId AND sdap.AppointmentId = src.AppointmentId AND sdap.ProviderIndex = 1
         LEFT JOIN dbo.Provider dp ON dp.AccountID = src.AccountId AND dp.ProviderID = src.ProviderId
         LEFT JOIN dbo.vLatestTreatmentStatusHistory ltsh ON src.AccountId = ltsh.AccountId AND src.TreatmentProcedureId = ltsh.TreatmentProcedureId
    /*+ BROADCAST(da) */
         INNER JOIN staging.DimAccount da ON da.AccountId = src.AccountId;
Here, scheduler.Appointment is also a topic that is mapped to a flink table. The problem is that I'm seeing that certain columns being populated from scheduler.Appointment (like AppointmentStatus and AppointmentLocationId) are not being populated properly during my initial sync (ie with a high event influx). What could be the problem?