<jupyter_start><jupyter_text>Getting StartedThis tutorial describes how to use Pandas-TD in Jupyter to explore data interactively.Set your API key to the environment variable ``TD_API_KEY`` and run "jupyter notebook":```$ export TD_API_SERVER="https://api.treasuredata.com/"$ export TD_API_KEY="<KEY>..."$ jupyter notebook```You can connect to your database by ``create_engine``:<jupyter_code>%matplotlib inline

import os
import pandas_td as td

# Set engine type and database, using the default connection
engine = td.create_engine('presto:sample_datasets')

# Alternatively, initialize a connection explicitly
con = td.connect(apikey=os.environ['TD_API_KEY'], endpoint=os.environ['TD_API_SERVER'])
engine = td.create_engine('presto:sample_datasets', con=con)<jupyter_output><empty_output><jupyter_text>You can run a query by ``read_td``:<jupyter_code>query = '''
select * from nasdaq limit 3
'''
td.read_td(query, engine)<jupyter_output><empty_output><jupyter_text>Or you can read an existing job result by ``read_td_job``:<jupyter_code>td.read_td_job(35809747, engine)<jupyter_output><empty_output><jupyter_text>You can read a table into a DataFrame by ``read_td_table``, optionally with specific time range and limit:<jupyter_code># Read from a table with time range
df = td.read_td_table('nasdaq', engine,
                      time_range=('2000-01-01', '2010-01-01'),
                      limit=10000)<jupyter_output><empty_output><jupyter_text>Importing a DataFrame into a table is also supported by ``to_td``:<jupyter_code># Create a DataFrame with random values
df = pd.DataFrame(np.random.rand(3, 3), columns=['x', 'y', 'z'])

# Import it into 'tutorial.import1'
con = td.connect()
td.to_td(df, 'tutorial.import1', con, if_exists='replace', index=False)<jupyter_output><empty_output><jupyter_text>Note that ``to_td`` currently uses Streaming API for imports.  It takes more than a few seconds before your data become visible.  Be patient.<jupyter_code># Check the result
td.read_td_table('tutorial.import1', engine)<jupyter_output><empty_output><jupyter_text>create_engine<jupyter_code>help(td.create_engine)<jupyter_output>Help on function create_engine in module pandas_td.td:

create_engine(url, con=None, header=True, show_progress=5.0, clear_progress=True)
    Create a handler for query engine based on a URL.
    
    The following environment variables are used for default connection:
    
      TD_API_KEY     API key
      TD_API_SERVER  API server (default: api.treasuredata.com)
      HTTP_PROXY     HTTP proxy (optional)
    
    Parameters
    ----------
    url : string
        Engine descriptor in the form "type://apikey@host/database?params..."
        Use shorthand notation "type:database?params..." for the default connection.
    con : Connection, optional
        Handler returned by connect. If not given, default connection is used.
    header : string or boolean, default True
        Prepend comment strings, in the form "-- comment", as a header of queries.
        Set False to disable header.
    show_progress : double or boolean, default 5.0
        Number of seconds to wait before printin[...]<jupyter_text>Examples<jupyter_code># presto
engine = td.create_engine('presto://APIKEY@api.treasuredata.com/sample_datasets')

# hive
engine = td.create_engine('hive://APIKEY@api.treasuredata.com/sample_datasets')<jupyter_output><empty_output><jupyter_text>``create_engine`` uses "default" connection if apikey and host are omitted.  In this case, the environment variables "TD_API_KEY" and "TD_API_SERVER" are used to initialize a connection:<jupyter_code># use default connection (TD_API_KEY is used)
engine = td.create_engine('presto:sample_datasets')<jupyter_output><empty_output><jupyter_text>If you prefer initializing a connection with custom parameters, you can use ``connect``:<jupyter_code># create a connection with detailed parameters (via tdclient.Client)
# See https://github.com/treasure-data/td-client-python/blob/master/tdclient/api.py
con = td.connect(apikey=os.environ['TD_API_KEY'],
                 endpoint=os.environ['TD_API_SERVER'],
                 retry_post_requests=True)
engine = td.create_engine('presto:sample_datasets', con=con)<jupyter_output><empty_output><jupyter_text>read_td_query<jupyter_code>help(td.read_td_query)<jupyter_output>Help on function read_td_query in module pandas_td.td:

read_td_query(query, engine, index_col=None, parse_dates=None, distributed_join=False, params=None)
    Read Treasure Data query into a DataFrame.
    
    Returns a DataFrame corresponding to the result set of the query string.
    Optionally provide an index_col parameter to use one of the columns as
    the index, otherwise default integer index will be used.
    
    Parameters
    ----------
    query : string
        Query string to be executed.
    engine : QueryEngine
        Handler returned by create_engine.
    index_col : string, optional
        Column name to use as index for the returned DataFrame object.
    parse_dates : list or dict, optional
        - List of column names to parse as dates
        - Dict of {column_name: format string} where format string is strftime
          compatible in case of parsing string times or is one of (D, s, ns, ms, us)
          in case of parsing integer timestamps
    distribute[...]<jupyter_text>Examples<jupyter_code>query = '''
select time, close from nasdaq where symbol='AAPL'
'''

# Run a query, converting "time" to a time series index
df = td.read_td_query(query, engine, index_col='time', parse_dates={'time': 's'})
df.plot()<jupyter_output><empty_output><jupyter_text>read_td_job<jupyter_code>help(td.read_td_job)<jupyter_output>Help on function read_td_job in module pandas_td.td:

read_td_job(job_id, engine, index_col=None, parse_dates=None)
    Read Treasure Data job result into a DataFrame.
    
    Returns a DataFrame corresponding to the result set of the job.
    This method waits for job completion if the specified job is still running.
    Optionally provide an index_col parameter to use one of the columns as
    the index, otherwise default integer index will be used.
    
    Parameters
    ----------
    job_id : integer
        Job ID.
    engine : QueryEngine
        Handler returned by create_engine.
    index_col : string, optional
        Column name to use as index for the returned DataFrame object.
    parse_dates : list or dict, optional
        - List of column names to parse as dates
        - Dict of {column_name: format string} where format string is strftime
          compatible in case of parsing string times or is one of (D, s, ns, ms, us)
          in case of parsing integer timestam[...]<jupyter_text>Examples<jupyter_code>import tdclient

# Before using read_td_job, you need to issue a job separately
client = tdclient.Client()
job = client.query("sample_datasets",
                   "select time, close from nasdaq where symbol='AAPL'",
                   type="presto")
# Get result and convert it to dataframe
df = td.read_td_job(job.id, engine, index_col='time', parse_dates={'time': 's'})
df.plot()<jupyter_output><empty_output><jupyter_text>read_td_table<jupyter_code>help(td.read_td_table)<jupyter_output>Help on function read_td_table in module pandas_td.td:

read_td_table(table_name, engine, index_col=None, parse_dates=None, columns=None, time_range=None, limit=10000)
    Read Treasure Data table into a DataFrame.
    
    The number of returned rows is limited by "limit" (default 10,000).
    Setting limit=None means all rows. Be careful when you set limit=None
    because your table might be very large and the result does not fit into memory.
    
    Parameters
    ----------
    table_name : string
        Name of Treasure Data table in database.
    engine : QueryEngine
        Handler returned by create_engine.
    index_col : string, optional
        Column name to use as index for the returned DataFrame object.
    parse_dates : list or dict, optional
        - List of column names to parse as dates
        - Dict of {column_name: format string} where format string is strftime
          compatible in case of parsing string times or is one of (D, s, ns, ms, us)
          in cas[...]<jupyter_text>Examples<jupyter_code># Read all records (up to 10,000 rows by default)
df = td.read_td_table("www_access", engine)
df.head(3)<jupyter_output><empty_output><jupyter_text>to_td<jupyter_code>help(td.to_td)<jupyter_output>Help on function to_td in module pandas_td.td:

to_td(frame, name, con, if_exists='fail', time_col=None, time_index=None, index=True, index_label=None, chunksize=10000, date_format=None)
    Write a DataFrame to a Treasure Data table.
    
    This method converts the dataframe into a series of key-value pairs
    and send them using the Treasure Data streaming API. The data is divided
    into chunks of rows (default 10,000) and uploaded separately. If upload
    failed, the client retries the process for a certain amount of time
    (max_cumul_retry_delay; default 600 secs). This method may fail and
    raise an exception when retries did not success, in which case the data
    may be partially inserted. Use the bulk import utility if you cannot
    accept partial inserts.
    
    Parameters
    ----------
    frame : DataFrame
        DataFrame to be written.
    name : string
        Name of table to be written, in the form 'database.table'.
    con : Connection
        Connection[...]<jupyter_text>Examples<jupyter_code># Create DataFrame with random values
df = pd.DataFrame(np.random.rand(3, 3), columns=['x', 'y', 'z'])<jupyter_output><empty_output><jupyter_text>``to_td`` fails if table already exists:<jupyter_code>td.to_td(df, 'tutorial.import1', con)
# Set "if_exists" to 'replace' or 'append'
td.to_td(df, 'tutorial.import1', con, if_exists='replace')<jupyter_output><empty_output><jupyter_text>Use ``index=False`` if you don't need to insert DataFrame index:<jupyter_code>td.to_td(df, 'tutorial.import1', con, if_exists='replace', index=False)<jupyter_output><empty_output><jupyter_text>``to_td`` inserts the current time as "time" column.  You can pass "time" column explicitly by ``time_col``:<jupyter_code>import datetime

df = pd.DataFrame(np.random.rand(3, 3), columns=['x', 'y', 'z'])

# Set "time" column explicitly
df['time'] = datetime.datetime.now()

# Use "time" as the time column in Treasure Data
td.to_td(df, 'tutorial.import1', con, if_exists='replace', index=False, time_col='time')<jupyter_output><empty_output><jupyter_text>If you are using a time series index, set ``time_index=0``:<jupyter_code>df = pd.DataFrame(np.random.rand(3, 3), columns=['x', 'y', 'z'])

# Set time series index
df.index = pd.date_range('2001-01-01', periods=3)

# Use index as the time column in Treasure Data
td.to_td(df, 'tutorial.import1', con, if_exists='replace', index=False, time_index=0)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09896263655558615, 'fraction_numerical': 0.008262186725419994, 'mean_word_length': 4.29091792132103, 'pattern_counts': {'":': 1, '<': 85, '<?xml version=': 0, '>': 85, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 2, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '364909', 'license': 'Apache-2.0', 'n_tokens_mistral': 3447, 'n_tokens_neox': 3339, 'n_words': 1068, 'path': 'doc/tutorial.ipynb', 'repo_name': 'tokubai/pandas-td', 'size': '10897'}