Distribute cron jobs to hours/minutes with Ansible

This is a handy trick to run a batch of cron jobs on different hour/minute combination so they won’t collide with each other and cause some pressure on the server.

The key is to use `with_indexed_items` and Jinja2 math:

- name: ansible daily cronjob {{ item.1 }}
  cron: user=ansible name=ansible-daily-{{ item.1 }} hour={{ item.0 % 24 }} minute= {{ (item.0 * 5) % 60 }}  job="/usr/local/run.sh {{ item.1  }}"
    - job_foo
    - job_bar

So the `job_foo` will be running at hour 0, minute 0 and `job_bar` will be running at hour 1, minute 5, etc…


MySQL/ Aurora to Google BigQuery

Google BigQuery(BQ) is Google’s column storage, it’s super fast when you need to query over many gigabytes of data.

BQ defaults to utf8, so it makes sense the tables in MySQL/ Aurora are also utf8 encoded.

I use Python and SQL Alchemy to load data to BQ. It’s a waste for SA’s ORM features but the raw connection in SA is nearly as fast as a naked MySQL connection, also I’m already familiar with SA.

First, make sure the MySQL URL has the correct encoding, eg. “db?charset= utf8”. It took my hours to figure out why some Russian characters became ??? without the charset set while some Spanish characters were fine.

There’s a number of ways to load data to BG, I have tried using REST API, or Google Storage. Using Google Storage is like 10x faster than REST, no surprise. Just like the Amazon S3 + Redshift combo. As for the data source format, JSON is modern and CSV is history because if there’s new line quoted in CSV, it will be limited to 4GB according to BQ’s documents.

BQ supports gzipped JSON which is sweet consider you only need to pay for 1/10 of the data traffic. This is how I stream MySQL output to gzipped JSON using ujson and gzip libraries.

with gzip.open(temp_dir + json_file, 'wb') as gzfile:
  for row in a_list_of_dictionaries:
    ujson.dump(row, gzfile, ensure_ascii=False)

Below is a snippet I used to map MySQL’s data types to BQ:

schema_sql = """select CONCAT('{"name": "', COLUMN_NAME, '","type":"', DATA_TYPE, '"}') as json from information_schema.columns where TABLE_SCHEMA = :database AND TABLE_NAME = :table;"""
fields = [] 
results = connection.execute(text(schema_sql), {'database':database, 'table': table} )
for row in results:
  field = ujson.loads(getattr(row, 'json'))
  if re.match('^[0-9]', field['name']):
    field['name'] = bq_prefix + field['name']
  if re.match('(bool|boolean)', field['type']):
    field['type'] = 'BOOLEAN'
  elif re.match('.*int.*', field['type']):
    field['type'] = 'INTEGER'
  elif re.match('.*(float|decimal|double).*', field['type']):
    field['type'] = 'FLOAT'
  elif re.match('.*(datetime|timestamp).*', field['type']):
    field['type'] = 'TIMESTAMP'
  elif re.match('.*binary.*', field['type']):
    field['type'] = 'BYTES'
    field['type'] = 'STRING'

Using gsutil is very simple, similar to s3cmd.

call(['gsutil', 'cp', json_file, bucket])

And later when I load the dumped JSON I can use these fields to rebuild the schema in BQ:

gbq_schema = ','.join([ "{0}:{1}".format(col['name'], col['type']) for col in fields ])
call(['bq', 'load', '--source_format', 'NEWLINE_DELIMITED_JSON', '--replace', gbq_table, gs_source, gbq_schema])

That’s about it. :)


Change Ganeti’s Network Configuration

Ganeti is a cluster virtual server management software tool built on top of existing virtualization technologies such as Xen or KVM and other open source software. ”

This is how I changed the secondary network configuration using Ganeti command line tools.

1, First, say I need to change the network from to, I first will remove all NICs from all instances. The following command will remove the last NIC from the instance:

gnt-instance modify --net remove <instance>

2, After all NICs have been removed from all instances, the network can be disconnected:

gnt-network disconnect <network>

3, Next is to remove the network CIDR from the cluster:

 gnt-network remove <network>

4, Re-add the network with the new CIDR:

gnt-network add --network= <network>

5, Re-connect the network to the cluster:

gnt-network connect<network> bridged <bridge>

6, Re-add the NIC from the new network to every instance:

 gnt-instance modify --net add:network=<network>,ip=pool <instance>

7, The new NIC won’t be effected until the instance is rebooted by Ganeti:

 gnt-instance reboot <instance>

8, Not sure if there’s a way to pickup the IP automatically, at least I can assign the IP manually by editing /etc/ network/ interfaces with the new IP.

9, Execute `ifup` to bring up the NIC. That’s it!  :)

Why are you trying so hard to fit in when you were born to stand out?