# Mysql to BigQuery Replication

- Google said views are creating issues
- Is it possible to avoid JSON table in the views ?
- Because of views, binlog is getting modified, and it creates problem in replication.
- There are binlog entries added because of views, and that is creating problem.
- Can we avoid views? Can we keep views in separate db? need to investigate.
- Next action items for google
- google Q1: add flag, shamanth already added it. Do we have to reply to them?

- Other options
- Udaya: Create write replicate and calculate values for views. (will it be realtime?)
- Palani: In the sync adapter itself write to both databases (mysql & bigQuery).
- Problem: Insert queries are too complicated.
- Solution: If we simplify the queries we can get it work.
- Solution: Define schema, write a library to generate queries (ORM style query generator).
- Mysql Upsert / BigQuery Insert

- Two clients waiting
- ?
- ?

- Main problem is
- Profile - Profile service taking time
- Can we cache data?
- Emails are getting delayed
- Profile service
- got introduced for ESP 2.0
- Get data from all places (behavioural data, CRM data, synced data)
- Do Identity resolution and return collected data
- Lot of time to retrive data
- Workflows
- Run asynchronously whenever event happens
- So we have to lookup queries (workflow will give some id to profile service).
- Since we query by Id, it should data immediately. why it is not happening now?
- Example: http://beta-cluster.convertcart.com:30261/74707836/profile-proxy/profile/deviceId/208938804.7502397625?requiredField=crm
- API call is called with deviceId
- You have to find out emails collected in this device
- With device Id, find out all other ids (primarily email Id)
- Sample Query: SELECT `domain_id`, `order_id`, `email`, `phone`, `items`, `currency`, `customer_id`, `created_at`, `order_total` FROM `order_magento1` WHERE domain_id = '74707836' AND (email = "[e-mail]")

+----+--------------------+--------------------+------------+--------+---------------------------------------------------+---------+---------+------------------------------------------------------------------------------------------------------------------------------------+-------+----------+-------------+
| id | select_type        | table              | partitions | type   | possible_keys                                     | key     | key_len | ref                                                                                                                                | rows  | filtered | Extra       |
+----+--------------------+--------------------+------------+--------+---------------------------------------------------+---------+---------+------------------------------------------------------------------------------------------------------------------------------------+-------+----------+-------------+
|  1 | PRIMARY            | <derived3>         | NULL       | ALL    | NULL                                              | NULL    | NULL    | NULL                                                                                                                               |  5928 |     1.00 | Using where |
|  3 | DERIVED            | magento1_orders    | NULL       | range  | PRIMARY,data_source_magento1_order,order_id       | PRIMARY | 66      | NULL                                                                                                                               | 23711 |    25.00 | Using where |
|  4 | DEPENDENT SUBQUERY | magento1_customers | NULL       | eq_ref | PRIMARY,data_source_magento1_customer,customer_id | PRIMARY | 200     | data_warehouse.magento1_orders.domain_id,data_warehouse.magento1_orders.data_sources_id,data_warehouse.magento1_orders.customer_id |     1 |   100.00 | Using where |
+----+--------------------+--------------------+------------+--------+---------------------------------------------------+---------+---------+------------------------------------------------------------------------------------------------------------------------------------+-------+----------+-------------+

WITH
    `order_magento1` AS(
    SELECT
        `data_warehouse`.`magento1_orders`.`domain_id` AS `domain_id`,
        `data_warehouse`.`magento1_orders`.`data_sources_id` AS `data_sources_id`,
        `data_warehouse`.`magento1_orders`.`order_id` AS `order_id`,
        `data_warehouse`.`magento1_orders`.`increment_id` AS `id`,
        `data_warehouse`.`magento1_orders`.`status` AS `status`,
        `data_warehouse`.`magento1_orders`.`status` AS `custom_status`,
        `data_warehouse`.`magento1_orders`.`grand_total` AS `order_total`,
        STR_TO_DATE(
            `data_warehouse`.`magento1_orders`.`created_at`,
            '%Y-%m-%d%T'
        ) AS `created_at`,
        STR_TO_DATE(
            `data_warehouse`.`magento1_orders`.`updated_at`,
            '%Y-%m-%d%T'
        ) AS `updated_at`,
        `data_warehouse`.`magento1_orders`.`billing_address` AS `billing_address`,
        `data_warehouse`.`magento1_orders`.`shipping_address` AS `shipping_address`,
        `data_warehouse`.`magento1_orders`.`customer_firstname` AS `first_name`,
        `data_warehouse`.`magento1_orders`.`customer_lastname` AS `last_name`,
        `data_warehouse`.`magento1_orders`.`customer_id` AS `customer_id`,
        `data_warehouse`.`magento1_orders`.`customer_email` AS `email`,
        (
        SELECT
            `data_warehouse`.`magento1_customers`.`telephone`
        FROM
            `data_warehouse`.`magento1_customers`
        WHERE
            (
                (
                    `data_warehouse`.`magento1_customers`.`customer_id` = `data_warehouse`.`magento1_orders`.`customer_id`
                ) AND(
                    `data_warehouse`.`magento1_orders`.`domain_id` = `data_warehouse`.`magento1_customers`.`domain_id`
                ) AND(
                    `data_warehouse`.`magento1_orders`.`data_sources_id` = `data_warehouse`.`magento1_customers`.`data_sources_id`
                )
            )
    ) AS `phone`,
    TRIM(
        BOTH '"'
    FROM
        JSON_EXTRACT(
            `data_warehouse`.`magento1_orders`.`payment`,
            '$.method'
        )
    ) AS `payment_method`,
    `data_warehouse`.`magento1_orders`.`global_currency_code` AS `currency`,
    `data_warehouse`.`magento1_orders`.`subtotal` AS `sub_total`,
    `data_warehouse`.`magento1_orders`.`tax_amount` AS `total_tax`,
    IF(
        (
            `data_warehouse`.`magento1_orders`.`coupon_code` IS NOT NULL
        ),
        TRUE,
        FALSE
    ) AS `is_coupon_applied`,
    CAST(
        `data_warehouse`.`magento1_orders`.`shipping_amount` AS DECIMAL(10, 2)
    ) AS `shipping_cost`,
REPLACE
    (
        `data_warehouse`.`magento1_orders`.`items`,
        '"qty_ordered":',
        '"quantity":'
    ) AS `items`,
    `data_warehouse`.`magento1_orders`.`state` AS `payment_status`
FROM
    `data_warehouse`.`magento1_orders`
WHERE
    (
        (
            `data_warehouse`.`magento1_orders`.`domain_id` <> ''
        ) AND(
            `data_warehouse`.`magento1_orders`.`order_id` <> ''
        ) AND(
            `data_warehouse`.`magento1_orders`.`data_sources_id` <> ''
        )
    )
)
SELECT
    `order_magento1`.`domain_id` AS `domain_id`,
    `order_magento1`.`data_sources_id` AS `data_sources_id`,
    `order_magento1`.`order_id` AS `order_id`,
    `order_magento1`.`id` AS `id`,
    `order_magento1`.`status` AS `status`,
    `order_magento1`.`custom_status` AS `custom_status`,
    `order_magento1`.`order_total` AS `order_total`,
    `order_magento1`.`created_at` AS `created_at`,
    `order_magento1`.`updated_at` AS `updated_at`,
    `order_magento1`.`billing_address` AS `billing_address`,
    `order_magento1`.`shipping_address` AS `shipping_address`,
    `order_magento1`.`first_name` AS `first_name`,
    `order_magento1`.`last_name` AS `last_name`,
    `order_magento1`.`customer_id` AS `customer_id`,
    `order_magento1`.`email` AS `email`,
    `order_magento1`.`phone` AS `phone`,
    `order_magento1`.`payment_method` AS `payment_method`,
    `order_magento1`.`currency` AS `currency`,
    `order_magento1`.`sub_total` AS `sub_total`,
    `order_magento1`.`total_tax` AS `total_tax`,
    `order_magento1`.`is_coupon_applied` AS `is_coupon_applied`,
    `order_magento1`.`shipping_cost` AS `shipping_cost`,
    `order_magento1`.`items` AS `items`,
    `order_magento1`.`payment_status` AS `payment_status`
FROM
    `order_magento1`