# Karte Import Integration [Learn more about KARTE Export Integration](/int/karte-export-integration). You can use the KARTE Data Connector to import the contents of *.tsv and* .csv files stored in your Google Cloud Storage (GCS) bucket into Treasure Data. # Prerequisites - Basic knowledge of Treasure Data - An existing Google Service Account - You also need to generate and obtain a JSON key file from [Google Developers Console](https://console.cloud.google.com/apis/credentials). See the [Generating a service account credential](https://cloud.google.com/storage/docs/authentication#generating-a-private-key) section of Google Cloud Platform’s documentation for additional information. # Use the TD Console to Create Your Connection You can use the TD Console to configure your connection. ## Create a New Connection Go to **Integrations Hub** > **Catalog** and search. Select KARTE. ![](/assets/image-20191002-135955.548637f02320160279902e01534c80bff33d9c46d6aeafd70b1275e532f0228a.028cedc9.png) ## Create a New KARTE Connector The following dialog opens. ![](/assets/image-20191002-140011.c17b6968f44c52e7afef9b09add1e5bf17ce82550831495eb039462783ab109c.028cedc9.png) Provide the required credentials. In the TD Console, you can use only a JSON key file to authenticate: - **JSON key file:** Copy and paste the contents of the JSON key file generated from the [Google Developers Console](https://console.developers.google.com/projectselector/apis/dashboard?pli=1&supportedpurview=project&project=&folder=&organizationId=) into this field. - **Application Name:** *KARTE* is the default value. KARTE is the Treasure Data chosen name for this connector. Select **Continue**. Specify a name for your data connector and select **Done**. ## Import KARTE Data to Treasure Data The Connections page displays. Select **New Transfer**. ![](/assets/image-20191002-140058.8c2c0aa361e2430417c046015e0fc83e6b87bfa47834ce0b757a5f530a1f7aa5.028cedc9.png) The following sections provide instructions to help you fetch, preview, transfer, schedule, and review details. ### Fetch from You need to register the information that you would like to ingest: - **Bucket**. Google Cloud Storage bucket name (Example: *your_bucket_name*). - **Path Prefix**. The prefix for target keys. (Example: *logs/data_*). - **Path Regex**. Specify the regexp to match file paths. If a file path doesn’t match with this pattern, the file is skipped. (Example: In the case of *.csv$* #, a file is skipped if its path doesn’t match with this pattern.). - **Start after path**. Inserts the last_path parameter so that the first execution skips files before the path. (Example: logs/data_20170101.csv). - **Incremental**. Enables incremental loading. If incremental loading is enabled, the config diff for the next execution will include the last_path parameter, and the next execution skips files before the path. Otherwise, last_path will not be included. **Example** Amazon CloudFront is a web service that speeds up static and dynamic web content distribution. You can configure CloudFront to create log files that contain detailed information about every user request that CloudFront receives. If you enable logging, you can save CloudFront log files, shown as follows: ``` [your_bucket] - [logging] - [E231A697YXWD39.2017-04-23-15.a103fd5a.gz] [your_bucket] - [logging] - [E231A697YXWD39.2017-04-23-15.b2aede4a.gz] [your_bucket] - [logging] - [E231A697YXWD39.2017-04-23-16.594fa8e6.gz] [your_bucket] - [logging] - [E231A697YXWD39.2017-04-23-16.d12f42f9.gz] ``` In this case, **Fetch from** setting should be as follows: - **Bucket**: your_bucket - **Path Prefix**: logging/ - **Path Regex**: *.gz$* (Not Required) - **Start after path**: logging/E231A697YXWD39.2017-04-23-15.b2aede4a.gz (Assuming that you want to import the logfiles from 2017-04-23-16.) - **Incremental**: true (if you want to schedule this job.) ### Preview You’ll see a preview of your data. For more information on how data preview works, review [About Data Preview.](https://docs.treasuredata.com/smart/project-product-documentation/about-data-preview) To make changes, such as set specified column name, select **Advanced Settings** otherwise, select **Next**. ![](/assets/image-20191002-140127.8ce28e8252806bedbc3a1e227e7110284f61c67830baff1d7203d2bd6878fb5b.028cedc9.png) ### Advanced Settings Advanced Settings allow you to edit properties. The following properties are available: - **Default timezone**. Changes the time zone of timestamp columns if the value itself doesn’t include time zone. - **Columns** - **Name**. Changes the name of the column. The column name supports lowercase alphabets, numbers, and “_” only. - **Type**. Type parses a value as a specified type and then stores the type after converting to TreasureData schema. - **boolean** - **long** - **timestamp**: Imported as string type at TreasureData (Ex. 2017-04-01 00:00:00.000) - **double** - **string** - **json** - **Total file count limit**. The maximum number of files to read. (optional) ### Transfer to Choose an existing or create a new database and table for the import. - **Mode**: Append or Replace. Select whether to append records to an existing table or replace your existing table. - **Partition Key Seed**: Choose the long or timestamp column as the partitioning time. It uses the upload_time with the add_time filter as the default time column. ![](/assets/image-20191002-140144.651aa572e865bc7573686c420447667aed867478fccb900fe2e2a7a7f9f0c1f6.028cedc9.png) ## When You can specify a one-time transfer or schedule an automated recurring transfer. Use the following parameters to set your schedule: - **Once now**. Run the job one time. - **Repeat** - **Schedule**. You can schedule with three options: *@hourly*, *@daily* and *@monthly* and custom *cron*. - **Delay Transfer**. Add a delay to the specified run time. - **Timezone**. supports extended time zone formats, for example, Asia/Tokyo. ![](/assets/image-20191002-140158.2a798952d67f014f0c0fed2c52ca77642e32826d73528cdd28cc9ca6e2f58c49.028cedc9.png) ## Details Create a name for your source and then select **DONE** to save your KARTE connector as a new source. ![](/assets/image-20191002-140209.6b755a05b3d89734c08d69c3347b3068adcd567d403707c3078424d8ddaf88f7.028cedc9.png) ## My Input Transfers Edit your existing jobs in My Input Transfers. To view the details of previous transfers for this data connector, select the jobs icon. ![](/assets/image-20191002-140218.f38854126ff8ca4e9bcc4f49571d27ca0e949fc9848a556272a34459949e0261.028cedc9.png) # Using the CLI to Configure the Connector Before setting up the connector, install the [Treasure Data Toolbelt](https://docs.treasuredata.com/smart/project-product-documentation/installing-and-updating-td-toolbelt-and-treasure-agent). ## Create Seed Config File (seed.yml) Prepare *seed.yml* with the following example and your JSON key file. You must also specify the bucket and target file names (or prefixes for multiple files). ``` in: type: karte bucket: sample_bucket path_prefix: path/to/sample_file # path the the *.csv or *.tsv file on your GCS bucket auth_method: json_key json_keyfile: content: | { "private_key_id": "1234567890", "private_key": "-----BEGIN PRIVATE KEY-----\nABCDEF", "client_id": "...", "client_email": "...", "type": "service_account" } out: mode: append ``` The Data Connector for KARTE imports all files that match a specified prefix. For example, path_prefix: `path/to/sample_` –> `path/to/sample_201501.csv.gz`, `path/to/sample_201502.csv.gz`, …, `path/to/sample_201505.csv.gz`. For more details on available *out* modes, see the Appendix below. ## Guess Fields (Generate load.yml) Use *connector:guess*. This command automatically reads the target file and intelligently guesses the file format. ``` $ td connector:guess seed.yml -o load.yml ``` Open up [*load.yml*](http://load.yml.to) to see the guessed file format definitions, including file formats, encodings, column names, and types. ``` in: type: karte bucket: sample_bucket path_prefix: path/to/sample_file auth_method: json_key json_keyfile: content: | { "private_key_id": "1234567890", "private_key": "-----BEGIN PRIVATE KEY-----\nABCDEF", "client_id": "...", "client_email": "...", "type": "service_account" } decoders: - {type: gzip} parser: charset: UTF-8 newline: CRLF type: csv delimiter: ',' quote: '"' escape: '' skip_header_lines: 1 columns: - name: id type: long - name: company type: string - name: customer type: string - name: created_at type: timestamp format: '%Y-%m-%d %H:%M:%S' out: mode: append ``` Preview how the system parses the file by using the *preview* command. ``` $ td connector:preview load.yml +-------+---------+----------+---------------------+ | id | company | customer | created_at | +-------+---------+----------+---------------------+ | 11200 | AA Inc. | David | 2015-03-31 06:12:37 | | 20313 | BB Imc. | Tom | 2015-04-01 01:00:07 | | 32132 | CC Inc. | Fernando | 2015-04-01 10:33:41 | | 40133 | DD Inc. | Cesar | 2015-04-02 05:12:32 | | 93133 | EE Inc. | Jake | 2015-04-02 14:11:13 | +-------+---------+----------+---------------------+ ``` The guess command requires more than 3 rows and 2 columns in the source data file because it guesses column definition using sample rows from source data. If the system detects your column name or column type unexpectedly, modify `load.yml` directly and preview again. The Data Connector supports parsing of "boolean", "long", "double", "string", and "timestamp" types. The preview command downloads one file from the specified bucket and displays the results from that file. This might cause a difference in results from the preview and issue commands. ## Execute Load Job Finally, submit the load job. It might take a couple of hours depending on the size of the data. Users need to specify the database and table where their data is stored. It’s also recommended to specify *--time-column* option, since Treasure Data’s storage is partitioned by time (see also [data partitioning](https://docs.treasuredata.com/smart/project-product-documentation/data-partitioning-in-treasure-data)). If you don’t specify the option, the Data Connector selects the first *long* or *timestamp* column as the partitioning time. The column type specified by --time-column must be either *long* or *timestamp* type. If your data doesn’t have a time column, add it using the *add_time* filter option. Find more details at add_time filter plugin. ``` $ td connector:issue load.yml --database td_sample_db --table td_sample_table \ --time-column created_at ``` The previs example command assumes you have already created *database(td_sample_db)* and *table(td_sample_table)*. If the database or the table do not exist in Treasure Data, this command cannot be successful. Instead, manually create the database and table or use the *--auto-create-table* option with the *td connector:issue* command to auto-create the database and table. ``` $ td connector:issue load.yml --database td_sample_db --table td_sample_table --time-column created_at --auto-create-table ``` The Data Connector does not sort records server-side. To use time-based partitioning effectively, sort records in files beforehand. If you have a field called *time*, you don’t have to specify the *--time-column* option. ``` $ td connector:issue load.yml --database td_sample_db --table td_sample_table ``` # Scheduling Run Times Using our high availability scheduler, you can schedule periodic Data Connector to run for incremental KARTE file imports. By using this feature, you no longer need a *cron* daemon on your local data center. For the scheduled import, the Data Connector for KARTE imports all files that match with the specified prefix (e.g. path_prefix: `path/to/sample_` –> `path/to/sample_201501.csv.gz`, `path/to/sample_201502.csv.gz`, …, `path/to/sample_201505.csv.gz`) at first and remembers the last path (`path/to/sample_201505.csv.gz`) for the next execution. On the second and subsequent runs, it only imports files that come after the last path in alphabetical (lexicographic) order. For example, `path/to/sample_201506.csv.gz`, … ## Create the Schedule You can create a new schedule with the *td connector:create* command. The following are required: - The schedule name. - The cron-style schedule. - The database and table where their data will be stored. - The Data Connector configuration file. ``` $ td connector:create \ daily_import \ "10 0 * * *" \ td_sample_db \ td_sample_table \ load.yml ``` We also recommend that you specify the *--time-column* option, because Treasure Data’s storage is partitioned by time (see also [Architecture](https://docs.treasuredata.com/articles/project-product-documentation/about-treasure-data+Architecture)). ``` $ td connector:create \ daily_import \ "10 0 * * *" \ td_sample_db \ td_sample_table \ load.yml \ --time-column created_at ``` The `cron` parameter also accepts three special options: `@hourly`, `@daily` and `@monthly`. By default, a schedule is set up in the UTC timezone. You can set the schedule in a timezone using -t or --timezone option. `--timezone` option only supports extended timezone formats like 'Asia/Tokyo', 'America/Los_Angeles' etc. Timezone abbreviations like PST, CST are *not* supported and might lead to unexpected schedules. ## List the Schedules You can see the list of scheduled entries by running the command *td connector:list*. ``` $ td connector:list +--------------+------------+----------+-------+--------------+-----------------+----------------------------+ | Name | Cron | Timezone | Delay | Database | Table | Config | +--------------+------------+----------+-------+--------------+-----------------+----------------------------+ | daily_import | 10 0 * * * | UTC | 0 | td_sample_db | td_sample_table | {"in"=>{"type"=>"karte", ... | +--------------+------------+----------+-------+--------------+-----------------+----------------------------+ ``` ### Show the Settings and Schedule History *td connector:show* shows the execution settings of a schedule entry. ``` % td connector:show daily_import Name : daily_import Cron : 10 0 * * * Timezone : UTC Delay : 0 Database : td_sample_db Table : td_sample_table Config --- in: type: karte bucket: sample_bucket path_prefix: path/to/sample_ auth_method: json_key json_keyfile: content: | { "private_key_id": "1234567890", "private_key": "-----BEGIN PRIVATE KEY-----\nABCDEF", "client_email": "...", "type": "service_account" } decoders: - type: gzip parser: charset: UTF-8 ... ``` *td connector:history* shows the execution history of a schedule entry. To investigate the results of each individual run, use *td job jobid*. ``` % td connector:history daily_import +--------+---------+---------+--------------+-----------------+----------+---------------------------+----------+ | JobID | Status | Records | Database | Table | Priority | Started | Duration | +--------+---------+---------+--------------+-----------------+----------+---------------------------+----------+ | 578066 | success | 10000 | td_sample_db | td_sample_table | 0 | 2015-04-18 00:10:05 +0000 | 160 | | 577968 | success | 10000 | td_sample_db | td_sample_table | 0 | 2015-04-17 00:10:07 +0000 | 161 | | 577914 | success | 10000 | td_sample_db | td_sample_table | 0 | 2015-04-16 00:10:03 +0000 | 152 | | 577872 | success | 10000 | td_sample_db | td_sample_table | 0 | 2015-04-15 00:10:04 +0000 | 163 | | 577810 | success | 10000 | td_sample_db | td_sample_table | 0 | 2015-04-14 00:10:04 +0000 | 164 | | 577766 | success | 10000 | td_sample_db | td_sample_table | 0 | 2015-04-13 00:10:04 +0000 | 155 | | 577710 | success | 10000 | td_sample_db | td_sample_table | 0 | 2015-04-12 00:10:05 +0000 | 156 | | 577610 | success | 10000 | td_sample_db | td_sample_table | 0 | 2015-04-11 00:10:04 +0000 | 157 | +--------+---------+---------+--------------+-----------------+----------+---------------------------+----------+ 8 rows in set ``` ## Delete the Schedule *td connector:delete* will remove the schedule. ``` $ td connector:delete daily_import ``` # Appendix ## File Name Rule NFC is the type of unicode normalization form required for the file name as described in this [article](https://en.wikipedia.org/wiki/Unicode_equivalence#Normal_forms) in order to reduce [filename encoding and interoperability problems](https://cloud.google.com/storage/docs/gsutil/addlhelp/Filenameencodingandinteroperabilityproblems.). Otherwise it can cause a matching error when compared with path_match_pattern or path_prefix. ## Modes for Out Plugin You can specify file import mode in *out* section of seed.yml. ### append (default) This is the default mode and records are appended to the target table. ``` in: ... out: mode: append ``` ### replace (In td 0.11.10 and later) This mode replaces data in the target table.Any manual schema changes made to the target table will remain intact with this mode. ``` in: ... out: mode: replace ```