CleverTap Export Integration

Copy

• Copy for LLM

  Copy page as Markdown for LLMs
• View as Markdown

  Open this page as Markdown
• Open in ChatGPT

  Get insights from ChatGPT
• Open in Claude

  Get insights from Claude
• Cursorに接続

  CursorにMCPサーバーをインストール
• VS Codeに接続

  VS CodeにMCPサーバーをインストール

SELECT
  IDENTITY,
  CAST(
 'objectid' AS VARCHAR
  ) AS objectId,
  name,
  email,
  'M',
  CAST(
 '+1234567890123456789' AS VARCHAR
  ) phone,
  '1995-05-15T00:00:00Z',
  CAST(
 email_pref AS BOOLEAN
  ) AS MSG_email,
  CAST(
 'true' AS BOOLEAN
  ) AS MSG_push,
  CAST(
 'true' AS BOOLEAN
  ) AS MSG_sms,
  CAST(
 'true' AS BOOLEAN
  ) AS MSG_whatsapp,
  CAST(
 json_parse('["Newsletters", "Promotions"]') AS arrayvarchar
  ) "Custom Value 1"
FROM
  clever_tap_upload_user_profiles LIMIT 1

ALTER TABLE source_data_table ADD COLUMN date_time_field VARCHAR;

INSERT INTO source_data_table (id, date_time_field)VALUES(1, '2024-08-28'),(2, '2024-08-28 15:30:00'),(3, '2024-08-28T15:30:00');

SELECT id, CAST(date_time_field AS timestamp) AS date_time_field FROM source_data_table;

INSERT INTO source_data_table (id, date_time_field)VALUES(1, '2024-08-28T00:00:00Z'),(2, '2024-05-28T15:30:00.123Z'),(3, '2024-02-28T15:30:00Z');

SELECT id, date_time_field FROM source_data_table;

ALTER TABLE source_data_table ADD COLUMN array_column ARRAYVARCHAR;

INSERT INTO source_data_table (id, array_column)VALUES(2, ARRAY['fig', 'grape', 'honeydew', 'kiwi', 'lemon']),(3, ARRAY['mango', 'nectarine', 'orange', 'pear', 'quince']),(4, ARRAY['raspberry', 'strawberry', 'tangerine', 'ugli fruit', 'vanilla bean']),(5, ARRAY['watermelon', 'ximenia', 'yellow apple', 'zucchini', 'apricot']);

SELECT id, array_column FROM source_data_table;

ALTER TABLE source_data_table ADD COLUMN array_string_column VARCHAR;

INSERT
  INTO
 source_data_table(
   id,
   array_string_column
 )
  VALUES(
 1,
 '["apple", "banana", "cherry", "date", "elderberry"]'
  ),
  (
 2,
 '["fig", "grape", "honeydew", "kiwi", "lemon"]'
  ),
  (
 3,
 '["mango", "nectarine", "orange", "pear", "quince"]'
  ),
),
(
  4,
  '["raspberry", "strawberry", "tangerine", "ugli fruit", "vanilla bean"]'
),
(
  5,
  '["watermelon", "ximenia", "yellow apple", "zucchini", "apricot"]'
)
;

SELECT id, CAST(json_parse(array_string_column) AS ARRAYVARCHAR) AS array_column FROM source_data_table;

SELECT id, CAST(json_parse(array_string_column) AS JSON) AS array_column FROM source_data_table;

{
  "type": "clever_tap",
  "account_id": "986-XXX-XXYY",
  "passcode": "XXYY",
  "region": "UNITED_STATES",
  "target_data_entity": "USER_PROFILES",
  "user_profiles_operation": "UPLOAD_USER_PROFILES",
  "array_operation": "REPLACE",
  "skip_invalid_record": true,
  "thread_count": 5
}

{
  "type": "clever_tap",
  "account_id": "986-XXX-XXYY",
  "passcode": "XXYY",
  "region": "UNITED_STATES",
  "target_data_entity": "USER_EVENTS",
  "skip_invalid_record": true,
  "thread_count": 5
}

{
  "type": "clever_tap",
  "account_id": "986-XXX-XXYY",
  "passcode": "XXYY",
  "region": "UNITED_STATES",    "target_data_entity": "USER_AUDIENCES_CUSTOM_LIST",
  "name_for_custom_list_upload": "demo name",
  "email_for_custom_list_upload": "demo@example.com",
  "segment_operation_mode": "UPDATE",
  "skip_invalid_record": true
}

td query \
  -d db_name \
  -w "select identity,
             objectid,
             name,
             email,
             gender,
             phone,
             dob,
             msg_email,
             msg_push,
             msg_sms,
             msg_whatsapp,
             CAST(json_parse('["Newsletters", "Promotions"]') AS JSON) AS 'Custom Value 1'
      from tbl_name" \
  --type presto \
  --result '{
    "type": "clever_tap",
    "td_authentication_id": 514830,
    "target_data_entity": "USER_PROFILES",
    "user_profiles_operation": "UPLOAD_USER_PROFILES",
    "array_operation": "REPLACE",
    "thread_count": 5,
    "skip_invalid_record": true
  }'

td query \
  -d db_name \
  -w "select objectId,
             Id,
             Type,
             p256dh,
             auth
      from tbl_name" \
  --type presto \
  --result '{
    "type": "clever_tap",
    "td_authentication_id": 514830,
    "target_data_entity": "USER_PROFILES",
    "user_profiles_operation": "UPLOAD_DEVICE_TOKENS",
    "thread_count": 5,
    "skip_invalid_record": true
  }'

td query \
  -d db_name \    -w "select identity
     from tbl_name" \
 --type presto \
 --result '{
   "type": "clever_tap",
   "td_authentication_id": 514830,
   "target_data_entity": "USER_PROFILES",
   "user_profiles_operation": "DEMERGE_USER_PROFILE",
   "thread_count": 5,
   "skip_invalid_record": true
 }'

td query \
 -d db_name \
 -w "select type,
            value,
            status
     from tbl_name" \
 --type presto \
 --result '{
   "type": "clever_tap",
   "td_authentication_id": 514830,
   "target_data_entity": "USER_PROFILES",
   "user_profiles_operation": "UPDATE_EMAIL_PHONE_SUBSCRIPTION",
   "thread_count": 5,
   "skip_invalid_record": true
 }'

td query \
 -d db_name \
 -w "select value
     from tbl_name" \
 --type presto \
 --result '{
   "type": "clever_tap",
   "td_authentication_id": 514830,
   "target_data_entity": "USER_PROFILES",
   "user_profiles_operation": "DISASSOCIATE_A_PHONE_NUMBER",
   "thread_count": 5,
   "skip_invalid_record": true
 }'

td query \
 -d db_name \
 -w "select identity,
            objectid,
            ts,
            evtname,
            product_name as 'Product name',
            category as 'Category',
            price as 'Price',
            currency as 'Currency',
            amount as 'Amount',
            payment_mode as 'Payment mode',
            delivery_by as 'Delivery By',
            items_0__category AS 'Items[0].Category',
            items_0__book_name as 'Items[0].Book name',
            items_0__quantity as 'Items[0].Quantity',
            items_1__category AS 'Items[1].Category',
            items_1__book_name as 'Items[1].Book name',
            items_1__quantity as 'Items[1].Quantity'
     from tbl_name" \
 --type presto \
 --result '{
   "type": "clever_tap",
   "td_authentication_id": 514830,
   "target_data_entity": "USER_EVENTS",
   "thread_count": 5,
   "skip_invalid_record": true
 }'

td query \
 -d db_name \
 -w "select type,
            identity
     from tbl_name" \
 --type presto \
 --result '{
   "type": "clever_tap",
   "td_authentication_id": 514830,
   "target_data_entity": "USER_AUDIENCES_CUSTOM_LIST",
   "name_for_custom_list_upload": "Test",
   "email_for_custom_list_upload": "test@example.com",
   "segment_operation_mode": "UPDATE",
   "skip_invalid_record": true
 }'