Is there a way to deal with this singularity in numerical integration?
It's easy to convert your integral into one without any singularities via the substitution $x=\sin\theta$: $$I=\int^{\pi/2}_0f(\sin\theta)\,d\theta.$$ If you're lucky to have a function $f$ that is even (so that $I$ is just a quarter of the integral over the full period, from $-\pi$ to $\pi$), you might be pleasantly surprised by the speed of convergence of the trapezoidal rule in this special situation.
A general technique is to remove the singularity by moving it to an analytically integrable function:
$$\int_0^1\frac{f(x)}{\sqrt{1-x^2}}dx=\int_0^1\frac{f(x)-f(1)+f(1)}{\sqrt{1-x^2}}dx=-\int_0^1\frac{f(x)-f(1)}{\sqrt{1-x^2}}dx+f(1)\left.\arcsin(x)\right|_0^1.$$
The limit of the integrand at $x=1$ should be finite.
For instance with $f(x):=x$,
$$\frac{x-1}{\sqrt{1-x^2}}=-\sqrt{\frac{1-x}{1+x}},$$ which is well-behaved.
The other answers so far focus on how to transform the integral so that you don't have a singularity. But that's not always viable so it's important to know how to deal with a singularity when one exists.
The trapezoid rule is a poor choice for this case, because it uses the values of the function at the endpoints, which can be infinite. Simposon's rule will have a similar problem.
More appropriate is the more primitive rectangle rule. That is, divide the domain to segments, and multiply each segment's width by the value of the function at the midpoint of the segment. This will work since you never try to sample the function at the infinite endpoints.
Alternatively, if you want faster convergence, you can use the more sophisticated and extremely powerful Gaussian Quadrature. More complicated but well worth knowing. Here, too, you divide the domain to intervals. For each interval, you sample the function at a few carefully chosen points to obtain surprisingly high accuracy.
There are different methods depending on the number of points in each interval, but the simplest is the 2-point method. And again, it will work with singularities, since the endpoints of intervals aren't sampled.
There is even a variant of Gaussian quadrature tailored specifically for a well-behaved function multiplied by $\frac{1}{\sqrt{1-x^2}}$, called Chebyshev–Gauss quadrature. To use it, you don't even have to divide to intervals - you just choose $n$ (the higher $n$, the better the accuracy) and you have
$$\int_{-1}^1\frac{f(x)}{\sqrt{1-x^2}}\approx\frac{\pi}{n}\sum_{i=1}^nf\left(\cos\left(\frac{2i-1}{2n}\pi\right)\right)$$
Which is very similar to transforming the integral as some other answers suggested. It will maximize the accuracy you can have for a given number of samples from the function $f$, and convergence is exponential.