SQLite
How to delete all tables from sqlite:
SELECT 'DROP TABLE ' || name || ';' FROM sqlite_master WHERE type = 'table';
Find duplicates by field “field_name”:
SELECT field_name, COUNT(field_name) AS cnt FROM some_table GROUP BY field_name HAVING(cnt > 1 ) FIXME - use cnt?
Find records changed in last 5 days:
SELECT * FROM some_table WHERE created_at >= NOW() - '5 day'::INTERVAL;
Get table definitions:
pragma table_info(mass_connections);
Export select query to csv:
.mode csv .output result_of_query.csv select * from my_table; .output stdout
Import data from csv into fresh new table:
.mode csv .import /path/to/your/all_data.csv new_table
Postgres
How to show all tables with sizes within database
SELECT schema_name, relname, pg_size_pretty(table_size) AS size, table_size FROM ( SELECT pg_catalog.pg_namespace.nspname AS schema_name, relname, pg_relation_size(pg_catalog.pg_class.oid) AS table_size FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid) t WHERE schema_name NOT LIKE 'pg_%' ORDER BY table_size DESC;
Show average amount of records per table
SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;
How to create data-only dump:
pg_dump -U your_pg_user -h pg_ip_address -p pg_port -a --column-inserts db_name > file_name.sql
pg_dump -U your_pg_user -h pg_ip_address -p pg_port -a --column-inserts --table=table_name db_name > file_name.sql
Useful pg_dump flags:
- – C adds the CREATE statements
- – s dump schema only
- – a dump schema & data
- – D dump using inserts (to simplify uploading data from PG into another db engine)
How to restore data:
psql dbname < infile.sql
PG stop/start:
$(PG_HOME)/bin/pg_ctl -D /data stop -m immediate
$(PG_HOME)/bin/pg_ctl start -D /data -l logfile
Aerospike:
Get settings:
asinfo -v 'get-config:'
Set particular settings:
asinfo -v 'set-config:context=service;batch-max-requests=10000000'
asinfo -v 'set-config:context=network;timeout=10000000'
asinfo -v 'set-config:context=service;batch-index-threads==100'
How to register LUA-script:
set LUA_USERPATH '.'
register module 'your_script_name.lua'
more at http://www.aerospike.com/docs/guide/aggregation.html
How to build secondary index based on bin
CREATE INDEX _idx ON . (bin_name) NUMERIC
bin_name ~ field name
How to delete all records within set:
https://github.com/aerospike/delete-set
Redis:
How to register lua script:
redis-cli script load "$(cat /YOUR/PATH/script_name.lua)"
Cassandra
How to save results of query to the file:
cqlsh -e"select * from table_name where some_txt_attr='very tricky string';" &gt; cassandra_file_query_result.txt
How to check node health:
nodetool status | awk '/^(U|D)(N|L|J|M)/{print $2}'
How to check compression ratio for particular table:
nodetool -h cassandra_ip cfhistograms some_keyspace some_table
How to check the dropped tasks count (at the bottom) at particular node:
watch -n 1 -d "nodetool tpstats"
How to do a “backup: of cassandra:
nodetool snapshot
It will generate snapshot of data at /<your path from yaml file>/data/snapshot.
How to do a “restore” from snapshot:
- restart the server
- copy data from snapshot to the respective keyspace folder
- delete content of every keyspace table at /<your path from yaml file>/data/
- stop cassandra
Kafka
How to show groups:
For old consumers:
./bin/kafka-consumer-groups.sh --list --zookeeper 1.1.1.1:2181
For new consumers:
./bin/kafka-consumer-groups.sh --list --new-consumer --bootstrap-server 1.1.1.1:9092
How to show how many messages:
./bin/kafka-run-class.sh kafka.tools.GetOffsetShell --broker-list 1.1.1.1:9092 --topic --time -1 --offsets 1
How to show how many messages in ALL partitions:
./bin/kafka-run-class.sh kafka.tools.GetOffsetShell --broker-list 1.1.1.1:9092 --topic --time -1 --offsets 1 | awk -F ":" '{sum += $3} END {print sum}'
How to show topics:
./bin/kafka-topics.sh --zookeeper 1.1.1.1:2181 --list
How to show consumer’s lag:
For old consumers:
./bin/kafka-consumer-groups.sh --describe --zookeeper 1.1.1.1:2181 --describe --group | column -s , -t
For new consumers:
./bin/kafka-consumer-groups.sh --describe --new-consumer --bootstrap-server 1.1.1.1:9092 --describe --group | column -s, -t
How to run test consumer:
./bin/kafka-console-consumer.sh --from-beginning --new-consumer --bootstrap-server 1.1.1.1:9092 --topic
RabbitMQ
How to create exchange and queue:
rabbitmqadmin declare exchange --vhost=/ name=test123 type=direct rabbitmqadmin declare queue --vhost=/ name=test_queue.123 durable=true rabbitmqadmin --vhost="/" declare binding source="test123" destination_type="queue" destination="test_queue.123" routing_key="test_routing_key"
HDFS
Updating file access control:
hdfs -setfacl -m user:user_name:rwx /Some/Path hadoop fs -chown user_name:group_name /Some/Path
Updating file access control recursive:
hdfs dfs -chown -R user_name:group_name /Some/Path
In case of following error:
>> java.io.IOException: Cannot obtain block length for LocatedBlock
Root cause: non-correctly closed files. How to fix them:
hdfs fsck /Some/Path -files -openforwrite | grep OPENFORWRITE hdfs debug recoverLease -path /Some/Path/corrupt.json -retries 5