= 2013-12-21 =

== Sqoop ==

 * <參考> [http://peicheng.pixnet.net/blog/post/28550495-mysql%E6%96%B0%E5%A2%9E%E4%BD%BF%E7%94%A8%E8%80%85 MySQL 新增使用者]
{{{
for ((i=1;i<=30;i++)); do printf "CREATE DATABASE user%02d;\n" $i; done > add_user_db.sql
mysql -h 主機名稱 -u root -p < add_user_db.sql
for ((i=1;i<=30;i++)); do printf "GRANT SELECT,INSERT,UPDATE ON user%02d.* TO 'user%02d'@'%s' IDENTIFIED BY '密碼';\n" $i $i '%'; done > add_user.sql
echo "FLUSH PRIVILEGES;" >> add_user.sql
mysql -h 主機名稱 -u root -p < add_user.sql
}}}
 * 建立 MySQL 測試資料庫
{{{
mysql> use test;
mysql> show tables;
mysql> create table mysql_data(id int primary key, name varchar(50));
mysql> show tables;
}}}
{{{
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| mysql_data     |
+----------------+
1 row in set (0.01 sec)
}}}
{{{
mysql> insert into mysql_data values (1, 'Hello');
mysql> insert into mysql_data values (2, 'World');
}}}
{{{
mysql> select * from mysql_data;
+----+-------+
| id | name  |
+----+-------+
|  1 | Hello |
|  2 | World |
+----+-------+
2 rows in set (0.01 sec)資料庫名
}}}
 * 建立測試帳號
{{{
mysql> GRANT SELECT,INSERT,UPDATE ON 資料庫名.* TO user@host IDENTIFIED BY '密碼';
mysql> FLUSH PRIVILEGES;
}}}
 * <參考> [http://finalfrank.pixnet.net/blog/post/32226901-%5B%E8%B3%87%E6%96%99%E5%BA%AB%E7%B3%BB%E7%B5%B1%5D-%E5%B0%87csv%E6%AA%94%E5%8C%AF%E5%85%A5mysql%E8%B3%87%E6%96%99%E5%BA%AB----%E7%94%A8comman 將 CSV 檔匯入 MySQL 資料庫 -- 用 Command Line]
{{{
mysqlimport -u root -p --lines-terminated-by="\r\n" --fields-terminated-by="," Dbname csvfile.csv
}}}
 * http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html
  * If you use LOAD DATA LOCAL in Perl scripts or other programs that read the [client] group from option files, you can add the local-infile=1 option to that group. However, to keep this from causing problems for programs that do not understand local-infile, specify it using the loose- prefix:
{{{
    [client]
    loose-local-infile=1
}}}
  * If LOAD DATA LOCAL is disabled, either in the server or the client, a client that attempts to issue such a statement receives the following error message:
{{{
    ERROR 1148: The used command is not allowed with this MySQL version
}}}
 * 執行 Sqoop 連到 MySQL 匯入資料的測試
{{{
jazz@master ~ $ sqoop import --connect jdbc:mysql://sql.3du.me/test --table mysql_data --username 帳號 -P
jazz@master ~ $ hadoop fs -cat mysql_data/part-m-00000
jazz@master ~ $ sqoop import --connect jdbc:mysql://sql.3du.me/test --table nyse_daily --username 帳號 -P -m 1
jazz@master ~ $ hadoop fs -cat nyse_daily/part-m-00000 | wc
}}}
{{{
wget http://download.microsoft.com/download/0/9/E/09E1D1A1-79F6-4BC2-969B-406EC77C7277/sqljdbc_4.0.2206.100_cht.tar.gz
tar zxvf sqljdbc_4.0.2206.100_cht.tar.gz
}}}
 * <參考> [http://serverfault.com/questions/338/adding-an-administrator-user-to-sql-server-2008 Adding an administrator user to SQL Server 2008]
{{{
CREATE LOGIN [LoginName] WITH PASSWORD = 'SomePassword' ;
}}}
 * <參考> [http://technet.microsoft.com/en-us/library/ms178630.aspx sp_changedbowner (Transact-SQL)] - 修改資料庫的預設擁有者
{{{
use [DatabaseName]; EXEC sp_changedbowner [LoginName];
}}}
 * <參考> 
{{{
ALTER LOGIN [LoginName] WITH DEFAULT_DATABASE = [DatabaseName]
}}}
 * <參考> [http://msdn.microsoft.com/en-us/library/ms188365.aspx BULK INSERT (Transact-SQL)] - 將 CSV 文字檔匯入到資料表
{{{
BULK INSERT NYSE_DAILY FROM 'c:\NYSE_daily.txt' WITH ( FIELDTERMINATOR ='\t', ROWTERMINATOR ='\n' );
}}}