def to_redshift(self, name, df, drop_if_exists=False, chunk_size=10000,
                    AWS_ACCESS_KEY=None, AWS_SECRET_KEY=None, s3=None,
                    print_sql=False, bucket_location=None, s3_bucket=None):
        if self.dbtype!="redshift":
            raise Exception("Sorry, feature only available for redshift.")
        try:
            from boto.s3.connection import S3Connection
            from boto.s3.key import Key
            from boto.s3.connection import Location

            # if boto is present, set the bucket_location to default.
            # we can't do this in the function definition because we're 
            # lazily importing boto only if necessary here.
            if bucket_location is None:
                bucket_location = Location.DEFAULT

        except ImportError:
            raise Exception("Couldn't find boto library. Please ensure it is installed")

        if s3 is not None:
            AWS_ACCESS_KEY = s3.access_key
            AWS_SECRET_KEY = s3.secret_key
        if AWS_ACCESS_KEY is None:
            AWS_ACCESS_KEY = os.environ.get('AWS_ACCESS_KEY')
        if AWS_SECRET_KEY is None:
            AWS_SECRET_KEY = os.environ.get('AWS_SECRET_KEY')
        if AWS_ACCESS_KEY is None:
            raise Exception("Must specify AWS_ACCESS_KEY as either function argument or as an environment variable `AWS_ACCESS_KEY`")
        if AWS_SECRET_KEY is None:
            raise Exception("Must specify AWS_SECRET_KEY as either function argument or as an environment variable `AWS_SECRET_KEY`")

        conn = S3Connection(AWS_ACCESS_KEY, AWS_SECRET_KEY)
        #this way users with permission on specific buckets can use this feature
        bucket_name = "dbpy-{0}".format(uuid.uuid4())
        if s3_bucket:
            bucket = conn.get_bucket(s3_bucket)
            bucket_name = s3_bucket
        else:
            bucket = conn.create_bucket(bucket_name, location=bucket_location)
        # we're going to chunk the file into pieces. according to amazon, this is
        # much faster when it comes time to run the \COPY statment.
        #
        # see http://docs.aws.amazon.com/redshift/latest/dg/t_splitting-data-files.html
        sys.stderr.write("Transfering {0} to s3 in chunks".format(name))
        len_df = len(df)
        chunks = range(0, len_df, chunk_size)

        def upload_chunk(i):
            conn = S3Connection(AWS_ACCESS_KEY, AWS_SECRET_KEY)
            chunk = df[i:(i+chunk_size)]
            k = Key(bucket)
            k.key = 'data-%d-%d.csv.gz' % (i, i + chunk_size)
            k.set_metadata('parent', 'db.py')
            out = StringIO()
            with gzip.GzipFile(fileobj=out, mode="w") as f:
                  f.write(chunk.to_csv(index=False, encoding='utf-8'))
            k.set_contents_from_string(out.getvalue())
            sys.stderr.write(".")
            return i

        threads = []
        for i in chunks:
            t = threading.Thread(target=upload_chunk, args=(i, ))
            t.start()
            threads.append(t)

        # join all threads
        for t in threads:
            t.join()
        sys.stderr.write("done\n")

        if drop_if_exists:
            sql = "DROP TABLE IF EXISTS {0};".format(name)
            if print_sql:
                sys.stderr.write(sql + "\n")
            self._try_command(sql)

        # generate schema from pandas and then adapt for redshift
        sql = pd.io.sql.get_schema(df, name)
        # defaults to using SQLite format. need to convert it to Postgres
        sql = sql.replace("[", "").replace("]", "")
        # we'll create the table ONLY if it doens't exist
        sql = sql.replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS")
        if print_sql:
            sys.stderr.write(sql + "\n")
        self._try_command(sql)
        self.con.commit()

        # perform the \COPY here. the s3 argument is a prefix, so it'll pick up
        # all of the data*.gz files we've created
        sys.stderr.write("Copying data from s3 to redshfit...")
        sql = """
        copy {name} from 's3://{bucket_name}/data'
        credentials 'aws_access_key_id={AWS_ACCESS_KEY};aws_secret_access_key={AWS_SECRET_KEY}'
        CSV IGNOREHEADER as 1 GZIP;
        """.format(name=name, bucket_name=bucket_name,
                   AWS_ACCESS_KEY=AWS_ACCESS_KEY, AWS_SECRET_KEY=AWS_SECRET_KEY)
        if print_sql:
            sys.stderr.write(sql + "\n")
        self._try_command(sql)
        self.con.commit()
        sys.stderr.write("done!\n")
        # tear down the bucket
        sys.stderr.write("Tearing down bucket...")
        for key in bucket.list():
            key.delete()
        if not s3_bucket:
            conn.delete_bucket(bucket_name)
        sys.stderr.write("done!")
Upload a dataframe to redshift via s3.
dataset/ETHPy150Open yhat/db.py/db/db.py/DB.to_redshift