cassandra 数据库

在写入库程序的时候 不知道cassandra怎么插入当前时间，Oracle 有个sysdate，百度了很久也没找到类似的例子。试了一下sysdate是不行的。最后发现原来直接插入“2017-07-17 12:00:00”也是可以的,神奇。

cassandra中插入图片文件等非文本值
网上搜基本上全是这篇的复制版本 有点看不懂 它里面

String columnName = "FileByteDatas";
String columnValue = parserFileNet("http://avatar.csdn.net/3/1/8/3_jemlee2002.jpg","jpg");
inf = cc.insertOneColumn(key, columnName, columnValue);

这个cc.insertOneColumn 方法里面是做的什么操作。我就需要这部分。

昨天按照官方的示例插入

CREATE TABLE bios ( user_name varchar PRIMARY KEY, 
   bio blob
 );

INSERT INTO bios (user_name, bio) VALUES ('fred', bigintAsBlob(3));

SELECT * FROM bios;

 user_name | bio
-----------+--------------------
      fred | 0x0000000000000003

This example shows how to use blobAsBigInt.

ALTER TABLE bios ADD id bigint;

INSERT INTO bios (user_name, id) VALUES ('fred', blobAsBigint(0x0000000000000003));

SELECT * FROM bios;

 user_name | bio                | id
-----------+--------------------+----
      fred | 0x0000000000000003 |  3
官方说了，见 https://datastax.github.io/python-driver/getting_started.html?highlight=blob ：

这个是没有问题的。现在要插入图片文件 我不知道怎么转换这个图片类型的
官方一段话是

Cassandra 1.2.3 still supports blobs as string constants for input (to allow smoother transition to blob constant). Blobs as strings are now deprecated and will not be supported in the near future. If you were using strings as blobs, update your client code to switch to blob constants. A blob constant is an hexadecimal number defined by 0[xX](hex)+ where hex is an hexadecimal character, such as [0-9a-fA-F]. For example, 0xcafe.

Blob conversion functions 

A number of functions convert the native types into binary data (blob). For every <native-type> nonblob type supported by CQL3, the typeAsBlob function takes a argument of type type and returns it as a blob. Conversely, the blobAsType function takes a 64-bit blob argument and converts it to a bigint value. For example, bigintAsBlob(3) is 0x0000000000000003 and blobAsBigint(0x0000000000000003) is 3.

参考
Blob conversion functions

These functions convert the native types into binary data (blob):
typeAsBlob(type)
blobAsType

这个

typeAsBlob 我试过byteAsBlob blobAsBlob 都报错
Exception in thread “main” com.datastax.driver.core.exceptions.InvalidQueryException: Unknown function blobas called

搜到这篇

cassandraOps.insert(new Person(myId, myAge, myName, myPict )); 这段代码也没有

搜到这篇
里面提到 Here is the sample code: https://gist.github.com/devsprint/5363023
但是这个页面打不开

往下看在页面中间部分发现一段代码

Yep, it worked like a charm. (PreparedStatement avoided the hex conversion)

But now, I'm seeing a few extra bytes come back in the select….
(I'll keep digging, but maybe you have some insight?)

I see this:
ERROR [2013-04-11 13:05:03,461] com.skookle.dao.RepositoryDao:
repository.add() byte.length()=[259804]

ERROR [2013-04-11 13:08:08,487] com.skookle.dao.RepositoryDao:
repository.get() [foo.jpeg] byte.length()=[259861]


(Notice the length's don't match up)

Using this code:
public void addContent(String key, byte[] data)

throws NoHostAvailableException {

LOG.error("repository.add() byte.length()=[" + data.length + "]");

String statement = "INSERT INTO " + KEYSPACE + "." + TABLE + "(key,
data) VALUES (?, ?)";

PreparedStatement ps = session.prepare(statement);

BoundStatement bs = ps.bind(key, ByteBuffer.wrap(data));

session.execute(bs);

}

public byte[] getContent(String key) throws NoHostAvailableException {

Query select = select("data").from(KEYSPACE, TABLE).where(eq("key",
key));

ResultSet resultSet = session.execute(select);

byte[] data = resultSet.one().getBytes("data").array();

LOG.error("repository.get() [" + key + "] byte.length()=[" +
data.length + "]");

return data;

}

按着这个改我的代码如下 cassandra插入图片

public void  insertBinaryTest(){
    CassBaseDao client = new CassBaseDao();
    client.connect();
    File f = new File("F:/Maobo_File_back/全国预警定制v0717优化.jar");
    byte[] fb = new byte[0];
    try {
         fb = FileUtils.image2byte("c:/壁纸back_Mb.jpg");
        System.out.println(fb.length);
    } catch (IOException e) {
        e.printStackTrace();
    }

    String cql="INSERT INTO simplex.bios (user_name, bio) VALUES ('xxfile', ?);"; //  ByteBuffer/byte[]  ByteBuffer.wrap(data))
    client.insertBinaryData(cql, ByteBuffer.wrap(fb));
    System.out.println("插入二进制");
    client.close();
}

其中queryBinaryData 方法如下
    public void insertBinaryData(String sql, ByteBuffer bytes){
        PreparedStatement prepareStatement = session.prepare(sql);
        BoundStatement bindStatement = new BoundStatement(prepareStatement).bind(bytes);
         session.execute(bindStatement);

    }

image2byte方法如下
 //图片转byte数组
    public static byte[] image2byte(String path){
        byte[] data = null;
        FileImageInputStream input = null;
        try {
            input = new FileImageInputStream(new File(path));
            ByteArrayOutputStream output = new ByteArrayOutputStream();
            byte[] buf = new byte[1024];
            int numBytesRead = 0;
            while ((numBytesRead = input.read(buf)) != -1) {
                output.write(buf, 0, numBytesRead);
            }
            data = output.toByteArray();
            output.close();
            input.close();
        }
        catch (FileNotFoundException ex1) {
            ex1.printStackTrace();
        }
        catch (IOException ex1) {
            ex1.printStackTrace();
        }
        return data;
    }

报这个错了，看起来有效果

Exception in thread "main" com.datastax.driver.core.exceptions.OperationTimedOutException: [/xx.xx.xx.xx:9042] Timed out waiting for server response

第二次运行还是报错

Exception in thread "main" com.datastax.driver.core.exceptions.ReadTimeoutException: Cassandra timeout during read query at consistency LOCAL_ONE (1 responses were required but only 0 replica responded)

这个应该是服务器没配好。没有报哪个转换异常了。

第三次运行

Exception in thread "main" com.datastax.driver.core.exceptions.NoHostAvailableException: All host(s) tried for query failed (tried: /xx.xx.xx.xx:9042 (com.datastax.driver.core.exceptions.TransportException: [/xx.xx.xx.xx:9042] Cannot connect))

放到服务器上跑的时候报错

No X11 DISPLAY variable was set, but this program performed an operation which requires it.

多跑几次 终于成功了 服务器上数据库还是要再配下

cassandra读取图片

/**
 * 获取cassandra中的图片
 */
public void getPIcFromCass(){
    CassBaseDao client = new CassBaseDao();
    client.connect();
    String cql="SELECT bio FROM simplex.bios";
    ResultSet results = client.queryDataRes(cql);
    for (Row row : results) {
        ByteBuffer   b = row.getBytes("bio");
        byte[] by = b.array();
        System.out.println("读取长度："+by.length);
        FileUtils.byte2image(by,"c:/fromcassRead.png");
        System.out.println();
    }
    client.close();
}

其中 byte2image方法如下 
  //byte数组到图片
    public static  void byte2image(byte[] data,String path){
        if(data.length<3||path.equals("")) return;
        try{
            FileImageOutputStream imageOutput = new FileImageOutputStream(new File(path));
            imageOutput.write(data, 0, data.length);
            imageOutput.close();
            System.out.println("Make Picture success,Please find image in " + path);
        } catch(Exception ex) {
            System.out.println("Exception: " + ex);
            ex.printStackTrace();
        }
    }

测试过插入和读取是没有问题的。demo完成了。网上这个cassandra插入和读取图片的详细的例子好像很少。

解决上午超时读写错误等报错的问题
增大jdk使用内存 也不知道这样操作对不对 /etc/profile里面增加了 JAVA_OPTS环境变量

JAVA_OPTS="-server -Xms10g -Xmx10g -Xmn1024m   -XX:PermSize=4g -XX:MaxPermSize=4g  -XX:+UseParallelOldGC -XX:+PrintGCDateStamps -XX:+PrintGCDetails -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/usr/local/tomcat/dumpfile/heap.bin  -Xloggc:/usr/local/tomcat7/logs/gc.log"

JAVA_HOME=/usr/local/jdk8/jdk1.8.0_101
JRE_HOME=$JAVA_HOME/jre
PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin
CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/lib
export JAVA_HOME JRE_HOME PATH CLASSPATH JAVA_OPTS

设置端口转发规则中增加了三个外网访问ip，之前是一个

跑了几次没有报错了。再观察